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’:
In this video you will learn how to access an API with Google Apps Script.
Chanel Greco has created this video tutorial which walks through some different ways you can interact with other sites with Google Apps Script to get data into Google Sheets. For the tutorial Chanel uses the Weather API and by coincidence Luxman Ravindrakumar has also shared a similar tutorial on Medium explaining how to use the OpenWeather API. So if you prefer learning by watching or reading you’ve no excuses :). Check the sources links below for both tutorials.
Are you using Google Drive to share information with your staff? As the number of staff members increases, it can become increasingly difficult to manage permissions properly. I faced a similar issue. As the members of the team changed from time to time, setting access rights for each folder became a cost that could not be ignored. So I used Google Apps Script to create a tool to synchronize the permissions management table with the actual permissions.
We’ve previously featured Inclu Cat’s posts on ways to deal with Google Apps Script’s 6-minute execution limit [Ref 1 & Ref 2]. These posts continue to prove to be incredibly popular and usually feature in our most read list. Even if you aren’t interested in programmatically managing Google Drive file/folder permissions this post is a nice example of using the authors LongRun solution to process large amounts of data with Google Apps Script. From the source link you can find the GitHub repo with all the code you need and setup instructions.
Discover how to interact with Coda webhook-based automations using Google Apps Script! In this post, we’ll explore different code patterns in both Coda’s formula language and Google Apps Script to send data contained in a Google Spreadsheet to a Coda table, and also perform other related tasks, using the new (May 2022) webhook-powered Coda automations.
For those unfamiliar with Coda:
Coda provides word-processing, spreadsheet, and database functions. It’s a canvas that blends spreadsheets, presentations, apps, and documents together. The software can integrate with third-party services like Slack and Gmail.
This post from Pablo Felip is a great opportunity to see what is possible using the new Coda webhook-powered automations. We’ve previously featured a couple of Coda/Apps Script integrations on Pulse, that have highlighted solutions using the Coda REST API and Coda Packs. This is the first we’ve seen using webhooks, which have a ‘no-code’ when/if/then setup interface. This route may provide the quick glue required for your Coda/Google Workspace integration. Pablo includes all the source code in the post as well as detailed instructions for setting up.
I quite often get called upon by clients to create Google Sheet templates that have Google Apps Script Automations bound to them. Sometimes these Google Sheets require an automated setup process to run things like gathering initial data, setting up time triggers, approving scopes connected to onEdit() or onOpen() triggers or renaming connected forms and their contents.
One of the challenges is getting new owners of the duplicated template Google Sheet to run the bound script before they dive into working on the Google Sheet. … After quite a lot of trial and error, I have devised a pretty solid approach that seems to have the most success in getting users to run through the authorisation of scopes and then run the startup script.
Scott Donald shares his approach for helping users get setup when first using a container bound script project in Google Sheets. The process has some nice features including revealing additional sheets, changing the active sheet and cell as well as removing the setup sheet. The post also highlights some useful tips and considerations as part of the user experience including the type and placement of supporting information.
I wanted to give a presentation to a group of people. In order to make the presentation more engaging & eliminate silences during the slideshow, I thought of conducting polls in between. … Since I am giving presentation using Google tools, I thought let’s try it out with Google Apps Script.
I thought this was a nice little Apps Script snippet from Prateek Sharma which hooks into a .onFormSubmit() trigger to update all charts embedded from Google Sheets in a Google Slides presentation. Prateek provides detailed steps including the code for setting this up. Unfortunately, a limitation of Google Slides is once you go into ‘slideshow’ mode all charts become static images so you need to jump back to the editor view to show real-time results.
This is a sample script for retrieving and parsing the XML data from Google Workspace Update Blog and putting it to Google Spreadsheet using Google Apps Script.
While this post from Kanshi Tanaike focuses on parsing the XML feed from the Google Workspace Update Blog the code can easily be modified to pull other XML feeds.
Even if you are not interested in parsing XML to Google Sheets the code pattern in this solution is worth looking at as it uses a destructuring assignment and reduce() to construct the .setValues() array for writing data to Google Sheets.
In Kanshi Tanaike’s script they clear the contents each time the script runs. If you would like insert new posts keeping a record of previous blog updates here is a forked version which will insert new data.
If you selected 1 or more rows, Publigo lets you generate a personalized document for those rows only or all visible rows in your sheet. Image credit: Romain Vialard
It is super easy in Google Sheets to select only specific rows, even if they are not adjacent. Simply hold down the CTRL / command key while making your selections. And those selections are also available in Google Apps Script via the method getActiveRangeList() … For Google Apps Script developers, here is a quick snippet showing how to process only selected rows.
Following the previous Pulse post on Get all selected Ranges in Google Sheets Romain Vialard got in touch to highlight the approach they have implemented to include this feature in the Mergo and Publigo Google Workspace Add-ons. This source post from Romain includes a snippet of code which might be useful for other Google Apps Script developers.
Get all selected Ranges in a Google Sheet along with values, row and column details.
Selected ranges in a Google Sheet
The following Google Apps Script was from some learning around multiple ranges being selected/highlighted by a user in a Google Sheet. I have always worked with individual cells or continuous ranges within Sheets, but during the development of my Archive Sheet data Add-on I wanted to enhance it by allowing more rows to be acted upon in one go.
I practiced getting a few values after looping through each selected range:
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Learn how to use the WhatsApp API to send personalized messages from Google Sheets to your WhatsApp contacts.
The question of how to automate WhatsApp using Google Apps Script has come up several times in the community forums. This has been notoriously difficult and also unreliable as Workspace developers had to often find workarounds without easy access to the WhatsApp Business API.
Send and receive messages using a cloud-hosted version of the WhatsApp Business Platform. The Cloud API allows you to implement WhatsApp Business APIs without the cost of hosting of your own servers and also allows you to more easily scale your business messaging.
Amit Agarwal hasn’t wasted any time in publishing a growing number of tutorials specifically for Google Apps Script developers and Google Workspace users on sending messages to WhatsApp. This first tutorial from Amit provides information on setting up a WhatsApp application on the Meta developers website and the Apps Script code required to send a message.
