AppsScriptPulse

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

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

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

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

Retrieving Google Docs summaries using Google Apps Script (hint: it’s using the existing Google Drive description property) 

This is a sample script for retrieving the summary of Google Document using Google Apps Script. Recently, a blog of Auto-generated Summaries in Google Docs has been posted. I thought that this is very interesting function. I thought that when this function is released, checking each summary of a lot of Google Document will be much useful for simply confirming the document content. And also, I thought that when all summaries can be retrieved using a script, it will be also useful. In this post, I would like to introduce to retrieve the summary of Google Document using Google Apps Script.

There are a couple of pieces of interesting information highlighted by this post by Kanshi Tanaike that caught my eye . First, useful to have a reminder that Google announced automatically generated summaries in Docs in February 2022. This might be a premium feature for the paid Workspace accounts as like Kanshi I’m not seeing this yet in my own free Google Workspace domain. The bit that really caught my eye is Google Docs summaries are using the existing Google Drive description property, which means it is not currently available via DocumentApp or if using the Google Docs API as an Advanced Service Docs. I’m sure this will catch some people out and hopefully this post will point people in the right direction.

Source: Retrieving Summary of Google Document using Google Apps Script

Building a Google Calendar add-on and publishing it in the marketplace

Image credit: Gareth Cronin

I’ve long been interested in using one of the big office productivity platforms to build an embedded add-on. For a solo developer it’s a no-brainer: the platform takes care of the requirements above, and when the platform has a marketplace, there’s even a ready to go distribution channel to take it to market! The only real reason I’ve resisted it so far is fear of the slog of platform approval processes and required collateral. In this story I’ll explain how I felt the fear and did it anyway 😀

Google OAuth verification and Workspace Add-on app review can be quite daunting the first time you go through the process. In this post from Gareth Cronin he shares his own personal journey getting his JIT Time Google Calendar Add-on verified and approved. There are some great tips and resources shared in the post including how to show the  OAuth client ID in the add-on demo video submitted as part of the verification processes.

Source: Building a Google Calendar add-on and publishing it in the marketplace

How to use Google Apps Script to process large amounts of data

Senbei is a traditional Japanese cracker made of rice. It is a very tasty cracker that goes well with Japanese tea, but there are also huge Senbeis as big as your face. What do you do when you eat such a big cracker? Usually, you would crush them into small pieces before eating them. The same can be said for processing huge data with Google Apps Script.

Some tips and tricks for handling script runtime and triggers total runtime quotas in Google Apps Script. Make sure you click through to another post from the author for code on An easy way to deal with Google Apps Script’s 6-minute limit.

Source: How to use Google Apps Script to process large amounts of data

[Fixed] Google Apps Script Web App HTML form file-input fields not in blob compatible format

Image by Tumisu from Pixabay

HTML form file-input fields are not converted to compatible blob format when submitted. They are left in application/octet-stream format. The file-input fields are passed as application/octet-stream, so DriveApp.createFile(formBlob) does not work correctly … So the functionality provided by HtmlService is not consistent with the documentation

There was an annoying little Google Apps Script issue which meant when handling file uploads to published Web Apps some additional coding was required rather than just sending the file blob server side. Thankfully the open issue ticket for this has recently been marked and confirmed fixed.

Source: HTML form file-input fields not in blob compatible format

Executing Function with Minutes timer in Specific Times using Google Apps Script [and tips on creating triggers in V8]

This is a sample script for executing a function with the minutes timer in the specific times using Google Apps Script. For example, when this sample script is used, the following situation can be achieved: Execute a function every 10 minutes only in 09:00 – 12:00 and 15:00 – 18:00 for the weekday.

A clever bit of coding from Kanshi Tanaike for running a Google Apps Script on a defined schedule. The part I found particularly interesting was the solution for programmatically setting triggers in the V8 runtime:

This sample script used Web Apps. Because when v8 runtime is used, when the trigger is set from the function executing by a trigger, the trigger is disabled. This is the recognized bug. But unfortunately, this has still not been resolved. (September 21, 2021) But, when the trigger is installed by Web Apps, no issue occurs. So, please deploy Web Apps.

For the sample code and explanation visit the source link.

Source: Executing Function with Minutes timer in Specific Times using Google Apps Script

How to call Google Cloud Run (or Cloud Functions) from Google Apps Scripts

Image Credit: bigchanchai/Shutterstock

I struggled how to make an authenticated call from Apps Scripts to Cloud Run even though I had owner rights on the GCP project. This post describes how to do it without reinventing the wheel.

Some very useful setup tips for using Cloud Run with Google Apps Scripts including additional references if you are keen to explore more.

Source: How to call Google Cloud Run (or Cloud Functions) from Apps Scripts

How to Print the Function Call Flow with Stack Trace in JavaScript/Google Apps Script – Digital Inspiration

The printStackTrace method of Java is useful for handling exceptions and errors during development. It tells you the exact line number in your source code and the file name where the problem occurred.

If you are working in the JavaScript / Google Apps Script world, you can use the console.trace() method to output the complete stack inside the web console ( or StackDriver logs for Google Scripts).

A better alternative is that you parse the stack property of the Error object. This contains the entire stack trace along with line numbers, column position and the function names.

Great debugging tip from Amit that you can use in Google Apps Script for parsing the entire stack trace along with line numbers, column position and the function names. Check the source link below for example code and usage.

Source: How to Print the Function Call Flow with Stack Trace in JavaScript – Digital Inspiration

Help! My time triggers are not in sync! : How to update your Google Apps Script project time zone. – Yagisanatode

If you have made it to this post you are probably a little frustrates about why your Google Apps Script time triggers are not running on or around the times that you have designated … To cut to the chase, the likely cause of clock triggers not running in the time range that you have selected is that the time zone in your Google Apps Script project is out.

Scott ‘Yagi’ Donald provides some very useful tips on how you can check and adjust your Apps Script project’s timezone.

Source: Help! My time triggers are not in sync! : How to update your Google Apps Script project time zone. – Yagisanatode