AppsScriptPulse

Beginner, Sheets

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

Tags:
| | 0 Comments |

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

Official News, Docs, Sheets, Slides

New Smart Chips (including AppSheet apps) plus third-party @menu resources in the Google Workspace Editors 

Tags:
| | 0 Comments |

There have been a number of recent updates and announcements around Smart Chips and other integrations to Google Docs, as well as Sheets and Slides. Some have just gone into the Google Workspace Developer Preview Program (DPP), whilst in the case of AppSheet smart chips for Google Docs they are generally available!

AppSheet smart chips for Google Docs

Starting with AppSheet smart chips for Google Docs if you already had the AppSheet Google Docs add-on installed you already have AppSheet smart chips enabled. More details including a link to the Workspace Marketplace Add-on are included in the related Workspace Updates post. The AppSheet smart chips are great way to integrate some of your app functionality in your Google Docs.

Preview links with smart chips in Sheets and Slides

We’ve had custom smart chip link previews for Google Docs for some time, but Google have recently added the same functionality to Sheets and Slides. If you have already developed link previews for Google Docs the good news is there isn’t much more you need to do to enable these for Sheets and Slides (mostly updating your manifest). More details are included on the updated Preview links with smart chips documentation page.

Create third-party resources from the @ menu

Finally, also gone into preview is third-party resource integration which you can use to “let users quickly create resources, such as tasks, support cases, or issues, in your service right from Google Docs. The new resource is then inserted into Google Docs as a smart chip.”. You can read more in the create third-party resources from the @ menu documentation page.

In the case of the preview features a reminder that this is your opportunity to test and give feedback to Google. Links to join and provide feedback are included in the Google Workspace Developer Preview page. Enjoy!

Solutions, Drive, Sheets, Intermediate

Uploading files without authorizing scopes  with a dialog in Google Sheets using Google Apps Script

Tags:
| | 0 Comments |

Making the shared users input a value and upload a file without authorization of the scopes with a dialog on Google Spreadsheet.

It’s usually unavoidable when you are creating and sharing Apps Script projects that the user will be required to complete an authentication flow to approve access to the services you include in your script such as reading/writing to Google Sheets, Drive etc.

The process is reliant on OAuth scopes, which are identifiers that specify the level of access an application requests from a user’s Google Account data. They are essentially a way for developers to define the specific actions or data their application needs to access. When a user grants an application access to their Google Account, they are agreeing to allow the application to perform the actions or access the data specified by the scopes.

Sometimes you can restrict the ‘scope’, for example, usually for Sheets, Docs, Slides, and Forms where I need only permission for the current doc I will include the following documented comment to only require access to the doc that the script project is bound to:

/**
 * @OnlyCurrentDoc
 */

There are some limitations when defining the scopes you need. For example if you would like a user to upload a document to Drive usually you would require the very broad https://www.googleapis.com/auth/drive scope which will prompt the user to ‘view and manage all of your Drive files’.

Understandably users may become nervous approving such a scope and in some cases Google Workspace Admins may prevent authentication for this type of scope for unverified/unconfigured applications.

There are alternative approaches to allowing users to execute Apps Script projects without having to approve scopes like Google Drive. There are clearly security considerations when you do this, so always proceed with caution.

This post from Kanshi Tanaike has some examples of how users can be prompted to upload files to Google Drive without authorising Drive access. The post includes two approaches, the first using a Web App which is pre authenticated to run as the user who has deployed the Web App, the other using a service account. The source post contains all you need to know include the code.

Source: Uploading Files without Authorizing Scopes by Shared Users with Dialog on Google Spreadsheet using Google Apps Script

Solutions, Drive, Gmail, Sheets, Intermediate

Build your own Gmail-based expense tracking solution with Google Sheets and Google Apps Script

Tags:
| | 0 Comments |

Use Google Apps Script to automate email-based expense tracking. Store and track your receipts entirely through Gmail, Drive and Sheets

Here’s a nice tutorial on how to create an email-based expense tracking system using Google Apps Script. The solution allows users to submit expense reports via email, which are then automatically processed and stored in a Google Sheet with attachments stored in Google Drive.

The blog post by Joshua Mustill provides detailed instructions on how to set up the system, including how to create the Gmail labels and filters, the Google Sheet and the Apps Script code. There are some nice features in the code you might want to use in other projects including the creation of date based Google Drive folders for storing Gmail attachments.

Source: Build your own email-based expense tracking with Google Apps Script

Snippet, Solutions, Docs, Sheets, Slides, Intermediate

Retrieve comments with emoji reactions from Google Docs, Google Slides and Google Sheet using Google Apps Script

Tags:
| | 0 Comments |

This report introduces the method for retrieving the Emoji reactions from the comments in Google Docs files (Google Documents, Google Slides, and Google Spreadsheets) using Google Apps Script.

Here’s a clever workaround by Kanshi Tanaike for retrieving comments with emoji reactions in Google Docs, Slides, and Sheets using Google Apps Script. The process to achieve this is a little convoluted in that Google Docs, Slides and Sheets are exported in Microsoft equivalent formats, then re-imported into Google formats. To remove some of the pain the post includes sample code snippets for achieving this, which can easily be adapted.

Source: Retrieve Comments with Emoji Reactions from Google Documents, Google Slides, and Google Spreadsheets using Google Apps Script

Snippet, Solutions, Sheets, Beginner

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

Tags:
| | 0 Comments |

 

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 📊

Snippet, Beginner, Sheets

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

Tags:
| | 0 Comments |

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

Snippet, Solutions, Sheets, Beginner

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

Tags:
| | 0 Comments |

 

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

Solutions, AI, Google Analytics, Sheets, Intermediate

Request Google Analytics data for Google Sheets using natural language with the PaLM API and Google Apps Script

Tags:
| | 0 Comments |

This video shows how to use the Palm API with Google Apps Script to extract data from Google Analytics 4 accounts. This can be useful for a variety of purposes, such as creating custom reports, automating data analysis, and building new data-driven applications.

Following on from the last post in Pulse where we looked at using Google PaLM API and MakerSuite in Google Apps Script, here’s another example from GDE Linda Lawton. As the video in the post shows Linda has been able to engineer prompts that allow you to use natural language to extract reports from Google Analytics. This shows the emergent capabilities of LLMs as well as some clever prompt engineering. The source post contains more detail, but here is an example:

var text = "The current date is '"+ date + "'. Create a JSON object which contains five parameter's dimension, metrics, start_date, end_date and property_id. The dimension and metric parameter's will be comma separated strings they can be empty if there is no valid text for it. The value of the dimension parameter should be a comma separated string of these dimensions names 'country, eventName, city, audienceName' and the value of the metric parameter should be a comma separated string of these metric names 'activeUsers, eventCount, screenPageViews', the property_id field will also be a string it will be a large number, start date and end date must be in the following format YYYY-MM-DD, which can be found in the given this text '" + prompt + "'. If no start date is found use set it to seven days ago and if no end date is found set it to today."

A couple of highlights worth noting:

  • Context – The current date is included programmatically to give the LLM a reference point
  • Reinforcement – ‘start date and end date must be in the following format YYYY-MM-DD’
  • Exceptions – ‘If no start date is found use set it to seven days ago and if no end date is found set it to today’

Source: GA4 + Palm API with Google App script

Solutions, Sheets, Intermediate

Efficiently deleting rows by conditions in Google Sheets with Google Apps Script

Tags:
| | 0 Comments |

In this report, I would like to introduce a sample script for efficiently deleting rows by conditions on Google Spreadsheet using Google Apps Script. Recently, I had a situation for being required to achieve this situation. In my report, it has already known that when Sheets API is used, the rows can be efficiently deleted by a condition. Ref However, in that case, Sheets API couldn’t be used. Under this situation, I came up with a method. In this report, I would like to introduce this method.

Here’s a clever method from Kanshi Tanaike for deleting rows in Google Sheets based on a column condition. The solution makes use of the built-in .removeDuplicates() method, the clever bit is the script first copies the header row into any row that matches the condition. As this creates duplicate rows the .removeDuplicates() method can be called to the entire data range. Using this method Kanshi was able to improve an execute of an earlier function from 67 seconds to 13 seconds!

Source: Benchmark: Efficiently Deleting Rows by Conditions on Google Spreadsheet using Google Apps Script