AppsScriptPulse

Clearing cells in multiple Google Sheets using Google Apps Script

This is a sample script for clearing the discrete cell values on multiple sheets using Google Apps Script.

Handy little code pattern for clearing ranges across multiple tabs in Google Sheets. Snippets are provided for both SpreadsheetApp and the advanced Sheets service.

Source: Clearing Discrete Cell Values on Multiple Sheets using Google Apps Script

Move column positions in Google Sheets with Google Apps Script

Move columns in a Google Sheet

Move columns in a Google Sheet

The following Google Apps Script is designed to reposition 2 columns within a Google Sheet. It is a small and simple bit of code but it was something new to me when I was collating lots of files together and appending columns at the end – which then needed to be moved.

In this example I am moving columns ‘collegename’ (E) and ‘shoesize’ (F) to the left of ‘postcode’ (D). So that ‘postcode’ will be the final column (F).

Source: The Gift of Script: Move Column Positions

Quickly format multiple Google Sheets with Apps Script

In this post, we’re going to see how we can very quickly format multiple Google Sheets with a little help from Apps Script.

The idea of this post, came from the Department of Education in Hawaii, where they have over 300 schools and a guy who works there told me he had to format over 300 Google Sheets the same way, and wondered how this could be done with Apps Script.

Easy I said! And I thought it was a typical example of how a little knowledge of Apps Script can help you speed up your work and allow you to spend less time doing boring, repetitive work, and focus more on the data itself.

Let’s see how it’s done!

This is a nice real world example of how Google Apps Script can be used to make a manual routine task more efficient. The post by Baz Roberts includes a detailed explanation of what is going on in the code making it also a great learning resource. If you have some more complicated Google Sheets formatting in mind you might want to also look at Google Sheets Macros, which lets you record every action you take within the spreadsheet creating a script you could call similar to the format functions included in the post.

Source: Quickly format multiple Google Sheets with Apps Script (also available on bazroberts.com)

GWAOw! 3 – Crop Sheet by Eric Koleda (how to keep Google Workspace Add-on development simple with custom menus)

Crop Google Sheet Data to the data range or a selection in 2 button click with Crop Sheet. Full Walkthrough

Does this Google Workspace Add-on developer story sound familiar? You are able quickly write the code that executes your add-on functionality, but then you spend days buried in HTML or Card Service making the UI. The Crop Sheet add-on by Eric Koleda highlighted in this latest GWAOw! episode is a great example of what is possible just by using custom menus. For what it is worth the source code (all 73 lines including comments!!!) is on GitHub. Follow the source link for the video demo from Scott Donald and links including the GitHub repo.

Source: GWAOw! 3 – Crop Sheet by Eric Koleda – Yagisanatode

Combining Google Apps Script with Google AppSheet

Combining Google Apps Script and AppSheet

In this blog I am going to show you how to combine Google Apps Script with Google AppSheet to make automation even more powerful.

Whilst Google AppSheet is marketed as a ‘no-code’ development platform for coders there have been a number of ways to add some customisations to AppSheet apps. Previously webhooks were the main way you could do this, but the recent Apps Script connector for AppSheet makes it possible to call and if required pass parameters from AppSheet into custom Apps Script functions.

If this is something you would like to learn more about Aryan Irani has continued his AppSheet tutorial series on Medium with this post which goes through the step-by-step process for setting up and using Apps Script code in AppSheet. Click through the source link to find out more.

Source: Combining Google Apps Script with Google AppSheet

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

Removing Google Drive file access permissions via a Google Apps Script Web App

Remove a users edit access to a Google Drive file via an Apps Script Web App.

Web App code sample

Web App code sample

The following Google Apps Script is designed to remove a person’s edit access from a Google Drive file via a Web App. The reason for using a Web App in this instance is because removing a person’s access to a file whilst they are running Apps Script code typically results in an error (or most certainly the inability to cleanly end the code). In the project I was working on prior to this step the code needs to send some automated emails before finishing with removing the person’s access.

Source: The Gift of Script: Remove File Access via a Web App

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

Automatically running a Google Apps Script function every quarter or another monthly period greater than one month

One of the great things about Google Apps Script is the way you can automate tasks. I’ve previously written about how I automate reporting and other examples like running backup processes. These usually run daily or once a month which is very straight forward to setup in Google Apps Script. If you want to run a script automatically every x number of months such as quarterly it gets a little harder. If you only want you script to run every three months … another option is to manage triggers programmatically which allows you to specify the date a function should be run again.

I’ve recently been revisiting some of my old Google Apps Script posts to do a bit of housekeeping. I thought this was a nice little snippet should you want to schedule a function to run on a time-driven trigger greater than one month. The trick used is to recursively create a time-driven trigger when the function is called. The solution comes with some caveats :)

Source: Running a Google Apps Script function every quarter or x months