AppsScriptPulse

Comment, Snippet, Beginner

Formatting date/time values using the user’s timezone in Google Apps Script

Tags:
| | 0 Comments |

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.

For displaying dates/time values for users in different time zones you can use Utilities.formatDate(date, timeZone, format), the challenge however is working out the user’s timeZone. For Google Workspace Add-ons developers can configure the manifest option to include user locale information in event objects included as part of action callback functions [See Accessing user locale and timezone].

For web apps and editor add-ons using HTML Service it is possible to get the user’s browser time zone setting with the JavaScript code Intl.DateTimeFormat().resolvedOptions().timeZone (at time of writing this works in 93.85% of browsers). This can be passed back to your server script and used to format date/times for the user’s time zone. This forked gist provides an example of how this can be used in Google Sheets (props to Eric Koleda for the original solution, which uses .getTimezoneOffset() to get the users time zone offset).

The merits of Intl.DateTimeFormat() and .getTimezoneOffset() are discussed in Getting the client’s time zone (and offset) in JavaScript.

If using HTML Service is not an option an alternative workaround is getting the user’s Google Calendar time zone using the Calendar Service and .getTimeZone(). Here is an example script shared on Stack Overflow:

function getUserTimeZone() {
  var userTimeZone = CalendarApp.getDefaultCalendar().getTimeZone();
  Logger.log(userTimeZone)
}

The downside of this approach is it potentially adds an additional authorisation scope the user will need to approve before the script can run. As noted by Google best practice is always to limit the scopes in your Apps Script projects to the ones you need.

If you’ve got other tips for handling user time zones feel free to pop them in the comments.

Comment, Solutions, Sheets, Beginner

Extract and visualize your own Twitter data using Google Apps Script and Google Sheets

Tags:
| | 0 Comments |

Use Apps Script to normalize your tweet data into tabular format for easy visualization & analysis.

I’ve a personal interest in Twitter data, in particular, how it can be collected analysed in Google Sheets so it was nice to see this example from Nick Young (@techupover). The solution shared by Nick uses Google Apps Script to parse a downloaded archive from a Twitter account and write it to a Google Sheet. A nice weekend project if you are looking for something to do :)

Source: Extract & visualize your own Twitter data using Google Apps Script & Google Sheets

Solutions, Drive, Sheets, V8, Intermediate

How to Auto-Download Podcasts to Google Drive with Google Sheets [and Google Apps Script] – Digital Inspiration

Tags:
| | 0 Comments |

You use Google Sheets as your own Podcast Manager that will automatically download your favorite podcasts to Google Drive and instantly sync across all your devices.

Amit Agarwal is always coming up with creative uses for Google Apps Script. His latest project is a great example of what can be achieved with a little code and a lot of know-how. Even if you aren’t a podcast fan this project is worth checking out as Amit is expert at writing concise and efficient code.

Some highlights to check out once you make a copy of the ‘Podcast Manager’ Google Sheet are use of CacheService for getting/putting a last update time and using the .filter(Boolean) trick for ignoring blank cells when using .getValues() on Google Sheets data. There is plenty more going on and worth spending some time using the Script Editor debugger and breakpoints to learn from a master.

Source: How to Auto-Download Podcasts to Google Drive with Google Sheets – Digital Inspiration

Gotchas, V8, Intermediate, Sheets

Report: Handling 10 million cells in Google Sheets using Google Apps Script

Tags:
| | 0 Comments |

In this report, I would like to introduce the important points for handling 10,000,000 cells in Google Spreadsheet using Google Apps Script.

In March 2022 Google announced that the Google Sheets cell limit is doubled from 5 million to 10 million cells. The increased capacity has implications for Google Workspace developers as you now may encounter scenarios where you have users with lots of data.

Fortunately, Kanshi Tanaike has been exploring the impact the increased volume of data in Google Sheets has when using Google Apps Script and both SpreadsheetApp and Sheets API. The linked report contains a number of useful findings and strategies for handling large Google Sheets with Apps Script.

Source: Report: Handling 10,000,000 cells in Google Spreadsheet using Google Apps Script

Add-on, Gotchas, Intermediate

Making of Webhooks for Sheets Workspace Add-on [and lessons learned using the Apps Script API]

Tags:
| | 0 Comments |

Behind the scenes look at what went into creating an Apps Script-native Add-on to generate Webhooks for Google Sheets. ICYMI: You can access the add-on from this link and know more about what it does here

It’s worth checking out Sourabh Choraria’s latest Google Sheets add-on which enables users to quickly setup a Google Sheet to receive data from other services which support the creation of webhooks. As part of this solution the add-on makes extensive use of the Apps Script API, which can be used to programmatically manage Apps Script projects including deployments. This post from Sourabh highlights some of the key endpoints used in the Apps Script API as well as a number of lessons learned about deploying web apps for users.

Source: Making of Webhooks for Sheets Workspace Add-on

Library, Solutions, V8, Intermediate

JSONata – JSON query and transformation language in Google Apps Script

Tags:
| | 0 Comments |

One of the benefits of Google moving Apps Script to the V8 engine is the possibility to drop in existing JavaScript libraries. Max Makhrov recently highlighted on Twitter how JSONata, which can be used to query and transform JSON data can be used in Google Apps Script:

JSONata is a lightweight query and transformation language for JSON data. Inspired by the ‘location path’ semantics of XPath 3.1, it allows sophisticated queries to be expressed in a compact and intuitive notation. A rich complement of built in operators and functions is provided for manipulating and combining extracted data, and the results of queries can be formatted into any JSON output structure using familiar JSON object and array syntax. Coupled with the facility to create user defined functions, advanced expressions can be built to tackle any JSON query and transformation task. – JSONata

JSONata is a solution better understood by trying it out, which you can do thanks to the script project shared by Max on Twitter or on the JSONata website. If you are interested in data query/manipulation solutions for Google Apps Script you might also want to check out our previous post on using AlaSQL for Apps Script.

Update: Max has published JSONata as an Apps Script library 

Solutions, Sheets, Intermediate

Use Google Sheets Apps Script to track Open Source GitHub and Docker statistics

Tags:
| | 0 Comments |

Image credit: Codenotary

If you run and maintain an Open Source project you’ll typically will want to keep track of things like your downloads, stars, commits over time, etc. to help you gauge engagement and overall health of your project. Here’s a quick way hack to keep track of some of this data in Google Sheet which will help you simplify the collection of data and help you better understand what that data means for your project.

This post was picked up by the official @WorkspaceDevs Twitter account, which has been highlighting and amplifying more community contributions recently (hint: you may want to follow/tag the account :)

The two things that caught my eye in this post. First, was the pattern used to get stats from multiple GitHub repos which are all appended in the same row (repo 1 cols B-F and repo 2 cols G-H).

Image credit: Codenotary

As a bit of a REST API/auth geek the second thing that interested me was the use of the Accept header, used by GitHub to specify the REST API version being called in UrlFetchApp.

Source: Use Google Sheets Apps Script to track Open Source GitHub and Docker statistics

Snippet, Beginner, Drive

Email notification of Drive file changes within last x hours

Tags:
| | 0 Comments |

Periodically check for any changes in a given Google Drive file and receive an email notification if there are any, within your given time-frame.

Email notification of Drive file changes. Image credit: Phil Bainbridge

The following Google Apps Script is designed to periodically check for any changes that have occurred to a given Google Drive file (ie edits to it) and send an email to notify of that. It goes through the Revision (Version History) of the file and looks at the modification dates for those that match with your given timeframe, to then collect the Username & Email address of the person that made those edits for inclusion in the email.

So as an example you may have a Google Sheet where you want to setup a check every 4 hours, to then be emailed if there have been any edits to it, along with who made those edits.

Source: The Gift of Script: Email notification of Drive file changes within last x hours

Solutions, Drive, Sheets, V8, Intermediate

Importing data from MS Excel to Google Sheet with Google Apps Script

Tags:
| | 0 Comments |

In this post we’re going to look at how we can get data from an Excel spreadsheet and import some of it into a Google Sheet. As an example, we’re going to upload an Excel which contains the current month’s royalty data from Amazon and add it to a central sheet which contains all the previous month’s royalties.

Similar to the post on converting a PDF document to text, this tutorial from Baz Roberts uses a similar approach of using the Google Drive API for file conversion, this time converting a MS Excel file into Google Sheets to make it possible to easily extract and update a master spreadsheet. The post includes a detailed explanation of the shared code.

Source: Importing Amazon royalty data from Excel to Google Sheet – Learning Google Workspace & Apps Script

Solutions, Drive, V8, Intermediate

How to extract text from PDF files with Google Apps Script – Digital Inspiration

Tags:
| | 0 Comments |

Image credit: Amit Agarwal

This tutorial explains how you can parse and extract text elements from invoices, expense receipts and other PDF documents with the help of Apps Script.

We’ve previously featured a method for extracting text from a PDF from Scott Donald. This latest post from Amit Agarwal uses a similar technique of sending a PDF document to Google Drive API to convert to a text file and then using RegEx to extract the content you need. Given the number of PDF documents flying around between organisations both of these posts from Amit and Scott are worth being aware of for potential future projects.

Source: How to Extract Text from PDF Files with Google Apps Script – Digital Inspiration