Handling date objects between Google Sheets with different timezones using Google Apps Script

This is a sample script for copying the date object between Google Spreadsheets with the different time zones using Google Apps Script.

As I’ve previously mentioned working with dates, times and time zones can often be a bit of a headache. If you’d like to learn more about some of the challenges of dealing with ‘big balls of wibbly-wobbly, timey-wimey… stuff’ I recommend watching Comptuerphile’s Problem with Time & Timezones.

This post from Kanshi Tanaike highlights a couple of approaches for handling date/time objects in Google Apps Script when you are using Google Sheets.

Source: Copy Date Object between Google Spreadsheets with Different Timezone using Google Apps Script

Dynamic chart heading in Google Sheets 📊 with a little Apps Script


Image script: Ben Collins'

Today we’ll see how to link a chart title to a cell, so that the chart title automatically reflects whatever value is in the cell

Today’s Pulse snippet comes courtesy of Ben Collins’ excellent Google Sheets Tips Newsletter, Tip 272. It uses a very basic onEdit() trigger to update a chart title based on a cell value. Ben has a great way of highlighting solutions without getting readers lost in complexity. Hopefully this example highlights the how easily you can modify Google Sheet charts using Apps Script.

If you are an Apps Script novice and looking for an easy way to learn what else is possible my own tip is to start the macro recorder, modify an embedded Google Sheets chart and then look at the resulting macro code in the Script Editor.

Source: Sheets Tip 272: Dynamic chart heading in Sheets 📊

Apps Script Pattern. Stop Script Execution on conditions from a Google Sheet

The common pattern for checking the business logic before executing automation

Here’s a clever little snippet from Max Makhrov which combines Google Apps Script with conditional logic created using Google Sheets functions, the resulting cell value being used for the error message.

 * @param {String} rangeName
 * @returns {Boolean} toStopExecution
function getStopMessageBoxFromNamedRange_(rangeName) {
  var ss = SpreadsheetApp.getActive();
  var r = ss.getRangeByName(rangeName);
  var v = r.getValue();
  if (v === '') {
    return false;
  var stopHeader = 'The script was stopped';
  Browser.msgBox(stopHeader, v, Browser.Buttons.OK);
  return true;

If after reading Max’s post you are unsure how this works, here is an example Google Sheet with some test data and logic as well as a ‘My Menu’ open to test the bound script.

Source: Apps Script Pattern. Stop Script Execution on conditions from Sheet

Creating and using a settings section in Google Sheets for your Apps Script projects


Image credit: Dimitris Paxinos

Create a reusable settings page in Google Sheets, using Apps Script, where configurations are easily accessible, even to those without a coding background.

I’ve not seen the numbers but would imagine the majority of Google Apps Script projects are script bound to Google Sheets. Google Sheets provide a useful data canvas which is familiar to users, which can also be used to quickly interface your script solutions. You could of course use dialogs and sidebars combined with the Properties Service to collect and store settings, but coding these in HTML/JavaScript can be time consuming.

This post from Dimitris Paxinos includes a nice Apps Script code pattern for getting and setting user settings from a Google Sheets tab. This includes some nice features including in memory storage and methods to use Script Properties. The post includes an accompanying video which explains the code should you need additional help understanding this solution.

Source: Creating a Settings Section in Google Sheets Apps Script Projects

Bringing JSON Data from an API into Looker Studio with Google Apps Script

Learn how to seamlessly integrate JSON data from an API into Looker Studio using a custom connector built with Google Apps Script.

This post is a useful reminder of that Google Apps Script can be used to make a data connector for Google online visualisation and reporting tool, Looker Studio. The post by Dimitris Paxinos covers all you need to know about integrating a third-party API as a data source, exposing configuration settings and deploying the connector. All the code is on Github and is a great boilerplate if you have other APIs you are interested in integrating.

Source: Bringing JSON Data from an API into Looker Studio

Autofill Google Sheet formula each day with Google Apps Script

Check a Google Sheet once per day and if the date is in the past Autofill another row of formulas.

Example screenshot of a Google Sheet with columns and dates that are used to perform calculations.

Autofill Google Sheet formulas each day

The following Google Apps Script is designed to check a Google Sheet once per day and if the date is in the past it Autofills another row with the existing formulas used across the columns. This post is a variation of the Autofill Google Sheet Formula one.

Source: The Gift of Script: Autofill Google Sheet Formula each day

Automatically save attachments for a Google Google to Google Drive using Gmail and Google Apps Script


Imagine you’re managing a Google Group, where important attachments are regularly sent. Manually saving these attachments to your Google Drive can be time-consuming and prone to errors. Is there a way to automate this process and ensure that you don’t miss any crucial documents?

It’s quite easy to tie yourself in knots when it comes to scheduling tasks in Google Apps Script to handle data created since the last run. This Medium post from Pablo Pallocchi shows a nice way your can structure Gmail searches using the after: operator combined with a ‘last execution date’ stored in Apps Script Properties Service. The result is a nice solution design to backup attachments sent to a Google Group to Google Drive. There’s lots of scope for extending and/or modifying this solution. All the details are in the source post.

Source: Automatically Save Email Attachments to Google Drive Using Google Apps Script

A Google Workspace Developer’s notes on publishing a Google Workspace Add-on to the Marketplace


This guide will walk you through creating a public Google Workspace Add-on, and launching it in the Google Workspace Marketplace for as free as possible. …

I’d never developed or published an add-on before. As I was looking into it, I realized that, while it is not super complicated, it is not readily obvious — especially for anyone just getting into add-on development. Google does have a guide on developing and publishing an add-on but it leaves a lot of unanswered questions.

So I thought I would put together what I learned in this guide — a playbook for anyone else who wants to develop their own add-on.

Following on from a recent Pulse post on How to publish to the Google Workspace Marketplace published by the Google Workspace team, here’s a developer’s take on the process from start to finish. As mentioned in the post the official support resources should be your start point, but these notes spotlight some of the nuances required to publish an add-on and the appendix includes some tips on naming your add-on and where/how to host required documentation including your add-on privacy policy.

Source: Developing a GAS Powered Google Workspace Add-on And Launching It To The Marketplace

How to publish to the Google Workspace Marketplace [Video Tutorial]

In this video you will find out how you can publish your app to the Google Workspace Marketplace.

Publishing a Google Workspace Add-on or Chat App can be a bit daunting. We’ve feature a couple of ‘how-to’ guides, which go into the detailed steps of the process, and my favourite has to be Alice Keeler’s top tips for getting your Google Workspace Add-on published. If you are still unsure about the Google Workspace Marketplace publication options and information required this video from Chanel Greco gives an overview of the entire process from start to finish.

Source: How to publish to the Google Workspace Marketplace

What can AI do for you as a Google Sheets user? Is the hype justified?

Image credit: Dall-E/ Ben Collins

See how AI tools work with Google Sheets to boost your productivity. Covers ChatGPT, Google Bard, and AI add-ons.

A very informative post from Ben Collins, discussing how AI can be used to automate tasks, identify patterns, and make predictions in the context of Google Sheets. Ben provides several examples of how Generative AI can be used to improve the way you can interact with data in spreadsheets from helping with formula to generating and improving data.

As Ben points out in the post users have benefited from AI in Google Sheets for a number of years with features like Explore. Perhaps the biggest recent change is exposing Large Language Models as a service, with users able to directly access the ‘prompt’ to generate output.

Source: AI + Google Sheets: How To Use Them Together