See four different API Authentication methods presented in Apps Script, including authentication in query string, headers, and OAuth2.
I got fed up digging around in my Drive folder for old scripts to refresh my memory on the syntax, so I created this reference.
It’s not a comprehensive post on how to connect to APIs, instead, it’s a short summary of common protocols for easy reference.(If you’re new to APIs, start with my Apps Script API tutorial for beginners.)
We are currently spoilt for choice with Google Apps Script community contributions. This is a great post from Ben Collins for Google Apps Script beginners highlighting different patterns used to interact with third party websites with APIs.
An API is essentially an interface that can be used by a computer programme to retrieve or interact with another application.
In this article, we’ll go over the different ways a GAS script can relate to the user using it, and show an easy way to dynamically resize dialogs built using the Apps Script HTML Service. To achieve this, we will introduce some basic concepts related to the object model of HTML documents and their manipulation using JavaScript. – [Google Translated]
A couple of community contributions for custom dialogs have landed in the Pulse inbox recently. The official documentation on Dialogs and Sidebars in Google Workspace Documents is an excellent starting point, but if you prefer learning from video tutorials Chanel Greco has recently published Google Apps Script Alert – Deep Dive on the saperis YouTube channel.
If you’d like to go a little further this post from Pablo Felip details how custom dialogs can be dynamically resized using the methods in google.script.host and client-side JavaScript (for non-Spanish speakers you’ll have to view this post via Google Translate).
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 :)
One the powerful features of Google Apps Script is with a couple of lines of code you can quickly start automating and organising your inbox. For people just getting started with Apps Script Google provide a introductory codelab ‘Accessing Google Sheets, Maps, and Gmail in 4 lines of code!’ and for those wanting to do a bit more the Google Workspace Developer documentation includes a ‘create a mail merge‘ solution.
Mail merge solutions are bit or a reoccurring theme in the world of Google Apps Script. In 2011 the official G Suite Developers Blog featured 4 ways to do Mail Merge using Google Apps Script with community contributions from James Ferreira, Steve Webster and Romain Vialard. The post references Romain’s ‘Yet another Mail Merge’ script, which he went on to develop as a very successful YAMM add-on before refocusing on the Mergo Mail Merge. Even further back when Google Apps Script was officially launched in 2009 this included a introductory video with a mail merge example.
A well as Google official channels the ability to automate your Gmail inbox has regularly caught the attention of the wider tech press. In 2013, Computerworld highlighted Jonathan Kim’s ‘Gmail No Response’ script which goes through your inbox and finds recent emails where you were the last respondent. Jonathan’s blog post is no longer available but the Gmail No Response’ script is on GitHub where it has been forked 100 times.
runs through the emails in your inbox and checks your outgoing messages for a question mark. Once it finds these emails it checks to see if they are in a date range and then sees if you have had a response. If you have not had a reply to your email containing a “?” then it adds the label “No Response”. You can then quickly see all of the threads for which you are awaiting a reply.
If you are interested in more Gmail script solutions then I highly recommend you have a look at content shared by Amit Agarwal. This doesn’t just include Apps Script solutions but also a number of Gmail tips and tricks. As many of these feature the way you can search your Gmail inbox they can also be used with GmailApp.search() or if using the Gmail Advanced Service Gmail.Users.Messages.list. A nice example of where a Gmail user tip can be used in Apps Script is Amit’s post which includes how to Search Emails by Specific Time in Gmail.
Iterate through a Google Drive folder of Microsoft Excel files and convert them into individual Google Sheet files.
Bulk convert Excel files to Google Sheets
The following Google Apps Script is designed to iterate through a Google Drive folder of Microsoft Excel files and convert them all to individual Google Sheet files.
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
Working with dates, times and time zones can often be a bit of a headache. If you’d like to learn more about some of the challenges of dealing with ‘big balls of wibbly-wobbly, timey-wimey… stuff’ I recommend watching Comptuerphile’s Problem with Time & Timezones.
For displaying dates/time values for users in different time zones you can use Utilities.formatDate(date, timeZone, format), the challenge however is working out the user’s timeZone. For Google Workspace Add-ons developers can configure the manifest option to include user locale information in event objects included as part of action callback functions [See Accessing user locale and timezone].
function getUserTimeZone() {
var userTimeZone = CalendarApp.getDefaultCalendar().getTimeZone();
Logger.log(userTimeZone)
}
The downside of this approach is it potentially adds an additional authorisation scope the user will need to approve before the script can run. As noted by Google best practice is always to limit the scopes in your Apps Script projects to the ones you need.
If you’ve got other tips for handling user time zones feel free to pop them in the comments.
Use Apps Script to normalize your tweet data into tabular format for easy visualization & analysis.
I’ve a personal interest in Twitter data, in particular, how it can be collected analysed in Google Sheets so it was nice to see this example from Nick Young (@techupover). The solution shared by Nick uses Google Apps Script to parse a downloaded archive from a Twitter account and write it to a Google Sheet. A nice weekend project if you are looking for something to do :)
