I’ve written a few articles about JavaScript proxying on here, and I’m a big fan. I also use a lot of APIs, and it can be time consuming to keep on checking the REST documentation for how to call them and deal with the UrlFetch responses. SuperFetch is a proxy for UrlFetchApp to help.
Bruce Mcpherson has been busy again and this latest post introduces ‘SuperFetch’ the new Google Apps Script library which works as a proxy for UrlFetchApp. SuperFetch has some useful additional functionality including: built in caching and compression, standard response format and error handling, and built-in JSON parsing.
SuperFetch also has some useful features often required when using third party APIs including delaying between requests and rate limiting. Bruce has promised some additional posts detailing more complex API configuration options including authentication.
If you selected 1 or more rows, Publigo lets you generate a personalized document for those rows only or all visible rows in your sheet. Image credit: Romain Vialard
It is super easy in Google Sheets to select only specific rows, even if they are not adjacent. Simply hold down the CTRL / command key while making your selections. And those selections are also available in Google Apps Script via the method getActiveRangeList() … For Google Apps Script developers, here is a quick snippet showing how to process only selected rows.
Following the previous Pulse post on Get all selected Ranges in Google Sheets Romain Vialard got in touch to highlight the approach they have implemented to include this feature in the Mergo and Publigo Google Workspace Add-ons. This source post from Romain includes a snippet of code which might be useful for other Google Apps Script developers.
Many of us undisciplined hacks (read: not professional developers) sometimes find ourselves wondering when we will buckle down and start using Github to store our Apps script source files and versions.
If you sometimes find yourself in the same boat: needing to restore or access an old script version, the first thing you probably do is revert back to the old Apps script editor (IDE), (filling out the form regarding the lack of version history as your reason), and then hoping the version queue goes back far enough for you to recover what you need.
Well, today my undisciplined friend, I will show you a way to recover your script files, all the way back to version 1!! Yes, no more switching back to the old code editor.
In Pulse we’ve previously highlighted Romain Vialard’s solution to Retrieve previous versions of Google Apps Script projects, which uses UrlFetchApp to make a call to the Script REST API and add recovered files to your Google Drive. In this new contribution from Clark Lind a clever ‘no-code’ solution using Google’s interactive API Explorer. The post includes all the steps you need to follow if you need to recover an old version of a script project.
Unit testing your code with this Apps Script Unit test library as you go along will makes it easy to immediately catch errors, and keeping a running test repertoire ensures that you don’t break anything.
When you start developing more complex Google Workspace solutions like add-ons and Chat apps it’s worth considering how you will test, debug and refactor your code. As noted in this post from Bruce Mcpherson the Apps Script community has published a number of different solutions/approaches to unit tests, a number of these appearing in Pulse. This latest post from Bruce looks at the bmUnitTest library he has developed highlighting how it can be set up and used.
Get all selected Ranges in a Google Sheet along with values, row and column details.
Selected ranges in a Google Sheet
The following Google Apps Script was from some learning around multiple ranges being selected/highlighted by a user in a Google Sheet. I have always worked with individual cells or continuous ranges within Sheets, but during the development of my Archive Sheet data Add-on I wanted to enhance it by allowing more rows to be acted upon in one go.
I practiced getting a few values after looping through each selected range:
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Learn how to use the WhatsApp API to send personalized messages from Google Sheets to your WhatsApp contacts.
The question of how to automate WhatsApp using Google Apps Script has come up several times in the community forums. This has been notoriously difficult and also unreliable as Workspace developers had to often find workarounds without easy access to the WhatsApp Business API.
Send and receive messages using a cloud-hosted version of the WhatsApp Business Platform. The Cloud API allows you to implement WhatsApp Business APIs without the cost of hosting of your own servers and also allows you to more easily scale your business messaging.
Amit Agarwal hasn’t wasted any time in publishing a growing number of tutorials specifically for Google Apps Script developers and Google Workspace users on sending messages to WhatsApp. This first tutorial from Amit provides information on setting up a WhatsApp application on the Meta developers website and the Apps Script code required to send a message.
There may be times when you want to present different content in your add-on’s homepage depending on certain conditions.
For example, I want to present a card to the user when they run my add-on for the first time (I’ll call it ‘card-A’), but present a different card for each subsequent time the add-on is run (‘card-B’).
Whilst this post from Clark Lind focuses on Gmail add-ons the code snippet could be used for other Google Workspace Add-ons for Calendar, Drive, Docs, Sheets and Slides that use the Card Service.
This is a report for obtaining the values from GOOGLEFINANCE using Google Apps Script. When I tested to retrieve the values from GOOGLEFINANCE function on Google Spreadsheet using Google Apps Script, I noticed that the values can be retrieved.
Another interesting report from Kanshi Tanaike where they test the ability to get values from the Google Sheets GOOGLEFINANCE function with Google Apps Script. For context, as highlighted in the report, Google announced in 2016 that historical data from the GOOGLEFINANCE function would no longer be accessible using either Google Apps Script or the Google Sheets API.
The 2016 announcement followed news in 2014 that the Finance Service, which allowed direct access to current and historical stock data, was deprecated (if you are interested in seeing what you are missing out on here is the Internet Archive snapshot of Finance Service from 2013).
So clearly there is a trend here in Google not wanting you to programmatically access stock data with their services and I anticipate the GOOGLEFINANCE function will be updated to prevent access from Google Apps Script, but in the meantime you can have some fun :)
Learn how to setup a video meeting inside Google Meet with the Google Calendar API and Apps Script. This Apps Script sample shows how you can programmatically schedule video meetings inside Google Meet with one or more participants using the Google Calendar API. It can be useful for teachers who wish to schedule regular meetings with their students but instead of manually creating meeting invites, they can easily automate the whole process for the entire class.
We’ve featured a couple of Google Meet scheduling solutions in Pulse[1,2]. This latest example from Amit Agarwal includes some nice code examples for additional optional parameters that can be used when using Google Calendar as an Advanced Service (Calendar.Events.insert). These include:
attendees[].responseStatus – for setting the attendee’s response status;
reminders.overrides[] – to override default event reminders and pops; and
recurrence[] – creating a custom recurrence for the calendar event
Amit includes some very useful examples of recurrence rules, using the RRULE notation. As well as RRULE the recurrence property can also accept RDATE and EXDATE values for further occurrence customisation.
Master the QUERY function, the most powerful function in Google Sheets, to become a more effective data analyst
Friend of Pulse and Google Sheets guru, Ben Collins, has a new course ‘The QUERY Function in Google Sheets’. The QUERY function it lets you perform various data manipulations making it easy to reshape, aggregate and explore your data in Google Sheets. The course is designed to be suitable for everyone from beginner to advanced who are interested in ways to work more effectively with your data.
If you are not familiar with the QUERY function Ben provides one example of what is possible in Sheets Tip 204: How To Use Dates In The QUERY Function (check the linked post in this Sheet Tip for an example worksheet).
Bonus:Sheets Tip 204 includes a 50% discount on the course valid until Friday 20 May 2022 at midnight EDT.
Finally, if you are interested in using the QUERY language in Google Apps Script it is possible! Below is some code used in this copy of Ben’s example workbook based on:
// based on https://gist.github.com/tanaikech/053d3ebbe76fa7c0b5e80ea9d6396011#sample-script
function myFunction() {
const doc = SpreadsheetApp.getActive()
const spreadsheetId = doc.getId(); // or set another Spreadsheet ID.
const sheetId = doc.getSheetByName('Data').getSheetId(); // or set another Sheet ID from Spreadsheet ID.
const query = "select C, B where B > date '2000-01-01' and B <= date '2002-12-31'"; // your QUERY
const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/gviz/tq?tqx=out:csv&gid=${sheetId}&tq=${encodeURI(query)}&access_token=${ScriptApp.getOAuthToken()}`;
const res = UrlFetchApp.fetch(url,);
console.log(res.getContentText());
const array = Utilities.parseCsv(res.getContentText());
console.log(array);
// SpreadsheetApp.getActiveSpreadsheet(); // This comment line is put for automatically detecting the scopes if directly adding a spreadsheet ID.
}
