AppsScriptPulse

Working with the Google Drive API Revisions history: Tips for handling revision merges with Google Apps Script

Having spent quite a bit of time working with the Google Drive API Revisions resource in this post I thought it would be useful to share some of the lessons and solutions I’ve picked up along the way. For this I’ll be sharing code snippets for interacting with the Revisions resource with Google Apps Script, but the solutions discussed could easily be applied to your programming language of choice.

Source: Working with the Google Drive API Revisions history: Tips for handling revision merges

How to access an API with Google Apps Script and API calls and Google Sheets manipulation

In this video you will learn how to access an API with Google Apps Script.

Chanel Greco has created this video tutorial which walks through some different ways you can interact with other sites with Google Apps Script to get data into Google Sheets. For the tutorial Chanel uses the Weather API and by coincidence Luxman Ravindrakumar has also shared a similar tutorial on Medium explaining how to use the OpenWeather API. So if you prefer learning by watching or reading you’ve no excuses :). Check the sources links below for both tutorials.

Sources: How to access an API with Google Apps Script (YouTube) and API Calls & Spreadsheet Manipulation With Google Apps Script

A tiny Google Apps Script tool to synchronize Google Drive permissions which can also process large amounts of data

Image credit: Inclu Cat

Are you using Google Drive to share information with your staff? As the number of staff members increases, it can become increasingly difficult to manage permissions properly. I faced a similar issue. As the members of the team changed from time to time, setting access rights for each folder became a cost that could not be ignored. So I used Google Apps Script to create a tool to synchronize the permissions management table with the actual permissions.

We’ve previously featured Inclu Cat’s posts on ways to deal with Google Apps Script’s 6-minute execution limit [Ref 1 & Ref 2]. These posts continue to prove to be incredibly popular and usually feature in our most read list. Even if you aren’t interested in programmatically managing Google Drive file/folder permissions this post is a nice example of using the authors LongRun solution to process large amounts of data with Google Apps Script. From the source link you can find the GitHub repo with all the code you need and setup instructions.

Source: [Google Drive] A tiny tool to synchronize permissions

Connecting ‘Tweet counts’ (or other APIs) to Data Studio using a Community Connector

A community connector with overridable config parameters to track recent Twitter trends for a query.

For the most part this connector is pretty similar to the one I’d created for visualising global stats of SendGrid user’s email statistics but with a key difference of making use of overridable config parameters — this is what gives the connector the capability to accept a query from the report directly and return/visualize the data based on a user’s input

This post from Sourabh Choraria is a useful reminder of what is possible with Data Studio Community Connectors, in this case connecting to the Twitter API v2 to visualise the tweet count for a defined search term.

The code for this connector is available from the source post and includes lots of inline comments to help you work out what is going on making it easy to modify if you have other APIs you would like to connect.

Source: Connecting ‘Tweet counts’ API to Data Studio using a Community Connector

Encouraging users to run setup scripts in Google Sheets container bound projects when they first make a copy

I quite often get called upon by clients to create Google Sheet templates that have Google Apps Script Automations bound to them. Sometimes these Google Sheets require an automated setup process to run things like gathering initial data, setting up time triggers, approving scopes connected to onEdit() or onOpen() triggers or renaming connected forms and their contents.

One of the challenges is getting new owners of the duplicated template Google Sheet to run the bound script before they dive into working on the Google Sheet. … After quite a lot of trial and error, I have devised a pretty solid approach that seems to have the most success in getting users to run through the authorisation of scopes and then run the startup script.

Scott Donald shares his approach for helping users get setup when first using a container bound script project in Google Sheets. The process has some nice features including revealing additional sheets, changing the active sheet and cell as well as removing the setup sheet. The post also highlights some useful tips and considerations as part of the user experience including the type and placement of supporting information.

Scott has also shared a solution for removing a Google Sheets button after a script has run, which you might want to also check out if you are thinking of developing this process further.

Source: One Approach to Encourage Users to Run Google Sheet-Bound Apps Script When They First Make a Copy of the File – Yagisanatode

Requesting to Gate API v4 using Google Apps Script (example of refactoring Python to Apps Script and signature requests)

Recently, I answered this thread. In that case, in order to convert the sample python script to Google Apps Script, the script for retrieving the signature might be a bit complicated. So, here, I would like to introduce this.

Gate.io is a cryptocurrency exchange which supports trading of a wide range of blockchain based currencies. The platform provides an API allowing users to interact and use features of Gate.io. Whilst it is unlikely that the majority of Google Workspace developers will be interested in interacting with the Gate API this post from Kanshi Tanaike might still be interesting to see how Python code has been refactored for Google Apps Script. It might also be useful should you encounter other APIs that require similar signature requests.

Source: Requesting to Gate API v4 using Google Apps Script

How to preserve formatting of Google Forms responses in Google Sheets with Google Apps Script

Learn how to automatically preserve the formatting in Google Sheet when new Google Form responses are submitted.

Handy little Apps Script snippet from Amit Agarwal should you need to keep any custom formatting applied to linked Google Forms responses in Google Sheets. Another way you can approach this is using ARRAYFORMULA to reference the form responses in another sheet and apply your desired formatting.

The default ‘Form responses’ sheet can be hidden if needed. Downside of using ARRAYFORMULA is you are referencing a cell range which can cause confusion when using features like sort. See the source link for all the code used in Amit’s solution.

Source: How to Auto Format Google Form Responses in Google Sheets – Digital Inspiration

A beginners API authentication cheat sheet for Google Apps Script

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.

What is an API?

If you would like to find out more about API Ben links to his API tutorial for beginners or I have shared workshop materials for ‘Machina a machina: An introduction to APIs with Google Sheets‘.

Check out the source link for Ben’s cheat sheet and other resources 👇.

Source: API Authentication Cheat Sheet for Apps Script

SuperFetch plugin – Firebase client for Google Apps Script – Desktop Liberation

Another in the SuperFetch (a proxy for Apps Script UrlFetchApp) plugins series, Frb is a plugin to access a Firebase Real time database.

If you want to take your use of APIs a little further Bruce Mcpherson is continuing his series exploring his recently published SuperFetch library showing how a client can be setup to interact with Firebase. As Bruce highlights: “Firebase is pretty fast, so there’s not a huge speed benefit from caching, but if you’re on a pay as go plan, SuperFertch caching can reduce your Firebase costs.”

The source post provided by Bruce provides everything to need to set up the SuperFetch client and Firebase project.

Source: SuperFetch plugin – Firebase client for Apps Script – Desktop Liberation

Retrieving and parsing XML RSS feeds in Google Sheets using Google Apps Script

This is a sample script for retrieving and parsing the XML data from Google Workspace Update Blog and putting it to Google Spreadsheet using Google Apps Script.

While this post from Kanshi Tanaike focuses on parsing the XML feed from the Google Workspace Update Blog the code can easily be modified to pull other XML feeds.

Even if you are not interested in parsing XML to Google Sheets the code pattern in this solution is worth looking at as it uses a destructuring assignment and reduce() to construct the .setValues() array for writing data to Google Sheets.

In Kanshi Tanaike’s script they clear the contents each time the script runs. If you would like insert new posts keeping a record of previous blog updates here is a forked version which will insert new data.

Source: Retrieving and Parsing XML data from Google Workspace Update Blog and Putting it to Google Spreadsheet using Google Apps Script