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

If you use clasp with Google Apps Script, you need this environment switching utility right now!

When working in a team and/or with a client, you want to have multiple environments. At minimum, you probably want a dev environment (or multiple ones) in which you are working, and a test environment in which the client or your team can run acceptance tests before production. Of course, they must both be separate from the production environment. To push your code to the correct environment, you need to either update the .clasp.json file manually or keep multiple copies of your script with different .clasp.json files. Fortunately, things have just become significantly easier, as I recently built an app for this purpose called clasp-env , which is available on NPM. See the source link for details.

Source: “If You Use Clasp With Google Apps Script, You Need This Utility Right Now”

Using Google Apps Script to convert a Google Sheets selected range to an image

Convert selected range into PNG image and save it to Drive

In this post from Max Makhrov you can discover how you can convert a selected Google Sheets range to an image. With options to embed charts and tables in Google Docs and Slides you might be asking why you would want to do this. Max highlights a couple of great reasons:

There are some limitations with this approach which are outlined in the post and if you have any solutions there is a link to contribute to the GitHub repo.

Source: Google Sheets Script. Range 2 Image

How to track link clicks in emails and more with Google Apps Script and Google Sheets

Learn how to track link clicks using Google Sheets and Apps Script to create a simple, lightweight tracking system

You live and learn! This is a regular occurrence if you are a subscriber to the work of Ben Collins (benlcollins.com). No exception with this recent post highlighting the ping attribute which can be used in <a> HTML links. This attribute has been around for a long time, but I’m sure many people like have never come across it. In the source post from Ben you can learn how to setup an Apps Script web app which will let you record link clicks in a Google Sheet.

A note of caution is whilst ping is valid HTML not all browsers choose to use it or enable it by default, something Mozilla have decided to do in Firefox. You can head over to Mozilla mdn documentation for the browser compatibility table and here is also an interesting post on how Google tracks with the ping-attribute.

Source: How To Track Link Clicks With Apps Script And Google Sheets

Introducing ‘Twitter for Google Chat’ – a Chat app built using Apps Script

Preview tweets and user data when sharing links, along with the ability to follow a specific user or like a particular tweet.

If you haven’t looked at Google Chat apps or not looked at Chat apps for a while this is a great post from Sourabh Choraria outlining the development process using Google Apps Script. As part of this Sourabh includes an overview and source code for a Twitter powered Chat app recently published and available to try out in your Google Workspace domain. The app showcases slash commands as well as a recent new feature of preview links.

Source: Introducing ‘Twitter for Google Chat’ – a Chat app built using Apps Script

Retrieving Smart Chip dropdown values from Google Docs using Google Apps Script

This is a sample script for retrieving the values of dropdown list of the smart chips on Google Document using Google Apps Script.

At August 23, 2021, 3 Classes for retrieving the smart chips have been added to Google Apps Script. But, in the current stage, unfortunately, all values of the smart chips cannot be retrieved by the Classes. For example, the dropdown list of the smart chips cannot be retrieved

Incredibly useful report and workaround from Kanshi Tanaike for Google Workspace Devs needing to get some ‘smart chips’ values from Google Docs. Hopefully classes/methods will be added to Apps Script and the Google Docs API (here is a related feature request you can star in the issue tracker), particularly as the current solution is to convert the Google Doc to .docx and then back to Google Doc.

Source: Retrieving Values of Dropdown List of Smart Chips on Google Document using Google Apps Script

Dependent dropdown lists for Google Sheets (and lots of other Google Apps Script goodies)

Updated the script for dependent lists, and now it supports new options. New for 2022:

Dependent drop-down lists can be used to create a dynamic list of choices in Google Sheets. When the user makes a selection from the first drop-down list, the choices in the second drop-down list will be updated based on the selection. This can be used to create a cascading list of choices.

If you are looking for a nice solution to build dependent drop-down lists in Google Sheets this is a great solution from Max Makhrov. For more experienced Google Apps Script developers the solution incorporates some clever snippets both from Max and a number of other members of the community. Some highlights include: converting a column index into corresponding column letter; a tasker to batch apply updates to a Google Sheet; ChuckyCache for objects above 100Kb; and a reference to a zip compression solution. All these are referenced in the source code provided in the post.

Source: Dependent Drop-Down Lists For Google Sheets v2022

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)

Automatically backup Google Drive folders to Cloud Storage with Google Apps Script

Image credit: Stéphane Giron

Few days ago, with the launch of French region ‘europe-west9’ in GCP, I made an apps script to backup a Drive folder to Cloud Storage. It is a cool script and works nicely but after some exchange, we can make it better.

So here I come back with onleebackup an open source code to backup multiple Google Drive folders to cloud storage with synchronisation.

A very interesting open source project from Stéphane Giron which lets you backup Google Drive folders to Google Cloud Storage. An important caveat is with Google Apps Script limitations like script runtime and URL Fetch POST size this won’t work if you have gigabytes of data. The code has some nice features like handling Google Docs/Sheets/Slides file types, converting them to equivalent MS Office formats as well as management of Google Drive shortcuts, which requires calls to v3 of the Google Drive API. The source post provides details for setting up onleebackup, which also includes a link to a previous post with code highlights.

Source: Automatically backup Google Drive folders to Cloud Storage

Benchmark: Process cost for HTML Template using Google Apps Script

Image credit: Kanshi Tanaike

When we use HTML in the Google Apps Script project, in order to show the values from the Google Apps Script side, the HTML template is used. When I used the HTML template with a large value, I understood that the process cost can be reduced by devising a script. In this report, I would like to introduce the process cost of the HTML template using the benchmark.

A great feature of Google Apps Script is the ability to create and serve custom HTML, often used to interface data you have in Google Workspace such as Google Sheets. Google highlight a coupe of different ways you can mix Apps Script code and HTML. Some of these ways are better in terms of process time and this report from Kanshi Tanaike highlights the cost of calling Apps Script functions as scriptlets in HTML templates. The good news is you can avoid delays in your web app rendering by making asynchronous calls with google.script.run , which you can read more about in Google’s best practices documentation.

Update: I’ve replicated this benchmark (smaller dataset) with google.script.run and it was only marginally slower (0.3s) than the ‘create HTML table with Google Apps Script’:

Source: Benchmark: Process cost for HTML Template using Google Apps Script