AppsScriptPulse

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

Create and run polls in Google Slides using Google Forms and Google Sheets

Image credit: Prateek Sharma

I wanted to give a presentation to a group of people. In order to make the presentation more engaging & eliminate silences during the slideshow, I thought of conducting polls in between. … Since I am giving presentation using Google tools, I thought let’s try it out with Google Apps Script.

I thought this was a nice little Apps Script snippet from Prateek Sharma which hooks into a .onFormSubmit() trigger to update all charts embedded from Google Sheets in a Google Slides presentation. Prateek provides detailed steps including the code for setting this up. Unfortunately, a limitation of Google Slides is once you go into ‘slideshow’ mode all charts become static images so you need to jump back to the editor view to show real-time results.

Source: Create and Run Polls in Google Slides using Google Forms & Google Sheets

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