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.
Gmail will not render base64 images embedded inside HTML emails but with Google Apps Script, you can use blobs to send the base64 encoded images
Base64 is a way of encoding binary data (like images) as a string of text. This makes it possible to embed images in webpages and HTML emails without hosting images on a server. An issue however, particularly for email, is that not all email services like Gmail support base64 encoded images.
Amit Agarwal’s latest blog post discusses a workaround for Gmail which involves using Google Apps Script to convert the base64 images to a blob and then embedding the blob in the email. There are some nice techniques in this post for handling/searching text strings with regular expressions as well as converting them to a Gmail friendly format.
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.
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.
This post is another example of the ‘power of the prompt’ and how LLM prompting strategies are a very effective way to utilise LLMs without having to ground or fine tune. If you are interested in understanding more here is a useful notebook produced by Michael W. Sherman which illustrates two powerful LLM prompting strategies: Chain of Thought and ReAct (Reasoning + Acting).
AppSheet is one one more tool 🛠for citizen developers and IT departments that helps automate concrete processes through a mobile-friendly and desktop UI’s.
In this post I’ll show you some AppSheet use-cases in the education industry, specifically how you can improve your school security using a simple app to keep the visits’s record and another app to automate and speed up the kids pick up.
In this post from Mozart GarcÃa you can find out about some use cases for improving school security using AppSheet. The post includes an overview of apps for recording visits and pickups. You can find similar apps in the AppSheet template gallery (Visitor Check-ins and Curbside Pickup), but with Duet AI for AppSheet there will soon be the opportunity to build you apps with an GenAI assistant. Below is a quick example based on one of Mozart’s app descriptions:
Duet AI in AppSheet is currently only available to Google Workspace customers. Google are rolling it out to a small group of customers at first, and then we will make it available to everyone who is eligible. You can read more about Create apps in AppSheet using Duet AI assistance
Learn how to conduct a pirate raid in Google Chat Spaces with Webhooks and Google Apps Script. Video Included.
Google continue to add and enhance Google Chat. The fun doesn’t stop at the end user experience, Google also adding functionality to the Google Chat API. A low-lying entry point for Google Workspace developers is using Google Chat webhooks. As a HTTP endpoint you can use your coding language of choice.
In this tutorial from Scott Donald you can learn how Google Apps Script can be used to interface Google Chat using webhooks. The post includes everything you need to get started including a tutorial video. There are some nice tips including how to post replies to a chat thread and formatting responses using the card service. Follow the link to start learning more…
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.
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’
This post has covered how you can quickly copy MakerSuite code examples to run them in Google Apps Script. To make the iterative process easier I’ve created GenerativeLanguageApp so that once you’ve created/saved an API key, you can drop these code snippets into your Google Apps Script project:
Generative AI (GenAI) is a rapidly developing field that has the potential to revolutionize many industries, including the way we work. Google has developed a number of LLMs that are generally available to developers, including foundation models trained for text, chat and code which are accessible as part of the PaLM 2 API. To help developers explore the capabilities of these models, Google has created the MakerSuite site.
In this post I share GenerativeLanguageApp, a Google Apps Script helper class which makes it easy to quickly copy MakerSuite code examples to run them in Google Apps Script. This is a great way to start experimenting with LLMs and seeing how they can be used in Google Workspace.
In the next part of this series, I’ll explore some of the capabilities of when using the PaLM 2 API with data from Google Sheets. Stay tuned!
