Tag: UrlFetchApp

Google Apps Script – URL Fetch JSON data from an API and add it to a Google Sheet – Ripple API example

Google Apps Script – UrlFetchApp, SpreadsheetApp, Ripple API, Time Triggers

I have been very fortunate of late to have the patronage of the Ripple XRP cryptocurrency community via XRP Tip Bot and Coil. This is no small part due to the support of user Recreational Rex from Twitter.

Full disclosure here, I don’t really know all that much about cryptocurrencies. I knew about Bitcoin and Ethereum but really didn’t really invest any time and money into looking at these growing forms of value exchange.

But now I’m a little curious.

I thought it would be fun to see what the value of XRP was against a fairly standard metric like the USD and keep a record of this exchange rate daily over a month or so (Mrs Yagi just read the ‘fun‘ in the last sentence and rolled her eyes).

I also thought it would be pretty cool to see how many exchange providers (Gateways) that conduct USD-XRP exchanges are around and see if there is much difference their exchange rate day-to-day among them.

Fortunately for me, the XRP Ledger provides a freely available open-source ledger that can be accessed via the Ripple Data API. The API can return JSON objects from which we can extract the data we need.

Of course, to record and display the daily exchange-rates I went straight to Google Sheets. I gathered the relevant XPR data using Google Apps Script’s UrlFetchApp Class and pushed it to my Google Sheet with SpreadsheetApp Class.

Here is a live embed of the XRP Google Sheet that is updated via a daily time trigger via Google Apps Script.

Continue reading “Google Apps Script – URL Fetch JSON data from an API and add it to a Google Sheet – Ripple API example”

Google Apps Script: Create an HTML email reminder service from Google Sheet data.

Google Apps Script: SpreadsheetApp, Utilities, MailApp, UrlFetchApp, HtmlService, onOpen

Ever wanted a quick way to send reminders to staff, letting them know that their task is overdue?

I often have to provide Google Sheet checklists that administration team managers can crosscheck to see if their team members have complete a certain task.

Reminding staff to complete a task can be time consuming and irritating. What if you could send an email to all those staff members who haven’t completed the task with a simple click of the button?

In this tutorial, we will learn how to create a Google Apps Script HTML email mail merge service that will send an email to a staff member who has not completed their task, indicated by a missing tick on a tickbox.

We will use the sample sheet below:

the badger Google Sheet

The Google Sheets reminder is triggered by a custom menu item.  You can see it at the top right of the image.

When the reminder is sent, it collects the rows of staff that have not indicated that they have completed the task.  It then sends an email to those staff members  with the reminder below:

HTML Email Reminder Google Apps Script
Get it! Badger! Ha!

As you can see, I’ve added some custom HTML to highlight the header and modify the width of the image. The email contains a number of unique pieces of data gathered from the Google Sheet Row:

  • The name of the user.
  • The deadline.
  • The name of the sheet (In this case, Tester).
  • The number of days overdue.

It also contains a link to the check sheet for the user to update once they have completed their task.

Back at The Badger Google Sheet, an alert window appears once the code is completed and also indicates any emails that could not be sent.

To get started you can Make a Copy of the Google Sheet by following this link:

Continue reading “Google Apps Script: Create an HTML email reminder service from Google Sheet data.”