Let’s extend Google Drive with Apps Script to create a simple add-on, use CardService for the UI, where we’ll select a few spreadsheets and pass them to the next card with navigation.
For those unfamiliar add-ons for Google Workspace come in a couple of flavours. The Google Editor Add-ons have been around the longest created with Google Apps Script and it’s HTML Service. Google Workspace Add-ons can also be created for the Google Editors (Docs, Sheets, Slides, Forms) as well as Gmail, Calendar and Drive using the Card Service with either Google Apps Script or another runtime environment. This post from Nibesh Khadka provides a great overview of how to create a Google Drive Add-on using Google Apps Script and the Card Service. It covers the basic setup with lots of references to relevant parts of the official developer documentation.
Use Google Apps Script to Autofill a formula down rows in a Google Sheet.
Autofill Google Sheet Formula
The following Google Apps Script is designed to insert a formula into the first row of data in a Google Sheet, then use Autofill to add the formula to the subsequent rows below. In this example I am just using a simple Sum formula to add up age and shoesize for demo purposes. The need to do this came about when combining multiple Google Sheet files however, that needed calculations adding to the final composed version.
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
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.
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).
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
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.
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.
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.
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.
Remove a users edit access to a Google Drive file via an Apps Script Web App.
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.
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
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.
