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.
Building a Support AI Assistant for Google Workspace using Apps Script, Gen AI, and Google Chat.
This post from Stéphane Giron highlights one approach for improving responses from LLMs by integrating Google Custom Search Engine responses into the prompt. In this example Stéphane used Google Apps Script to power the AI Assistant, integrating with Google Chat for the user interface and Cloud Functions to reformat data.
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
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 report introduces the method for managing the histories of the Google Apps Script project.
On August 23, 2023, the project history has been implemented in the new IDE of Google Apps Script. In the current stage, the users can see the history of the previously deployed script version. … In the case of the classic IDE, the users had been able to see the previously saved script version regardless of the deployment and just the save of the script. This is not implemented in the new IDE.
In August 2023, Google updated the online Apps Script Editor adding a feature which lets you view previously deployed script versions and compare them to the current script version. A current limitation the history is limited to versioned deployments. As noted in this source post from Kanshi Tanaike, deployments require several steps and you have to remember to go through the process. To make the process easier Kanshi has published a ScriptHistoryApp library, which can be used to create a custom web interface for making your own Apps Script project version history. Perhaps more usefully you can also manage snapshots of your script projects by fetching a URL. This makes it easy for you to either regularly save a project on a timed trigger or an event based mechanism.
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!
I recently faced a frustrating issue when writing data to a CloudSQL database with the JDBC class in Apps Script. I kept getting the following error:
Exception: Argument too large: SQL
I also observed that it only happened when my SQL query reached a certain length. I considered breaking it down into multiple queries, but I was still puzzled 🤔 because I was only sending a few dozen kilobytes of data.
Now, the thing is, the official documentation could be more helpful; even though the solution is there, it needs to be better explained. So, I turned to StackOverflow. There was a discussion on this exact topic, but to my surprise, I was still waiting for an answer. Until, well, I wrote it 😉
This guide is not documentation, but a condensed reminder of what you must do.
Max Makhrov has put together this very succinct guide to publishing Google Workspace Editor Add-ons to the Google Workspace Marketplace. As explained by Max the guide isn’t a step-by-step tutorial, but instead provides the key steps you should remember to do. There are some great tips in this post, particularly around what to include in the recorded screencast required by the Google oAuth verification team.
Unfortunately, there are no built-in methods for directly managing PDF data using Google Apps Script. Fortunately, after the V8 runtime has been released, several raw Javascript libraries could be used with Google Apps Script. pdf-lib is also one of them. When this is used, PDF data can be cooked over Google Apps Script. In this report, I would like to introduce achieving this using a Google Apps Script library.
Google Apps Script is a powerful tool for automating tasks. It can be used to process data, create spreadsheets, and send emails. One of the limitations of Apps Script is that it does not have built-in support for PDF files. However, there are a number of third-party libraries that can be used to work with PDF files and in Pulse we’ve featured a number of posts from Kanshi Tanaike where they have explored the pdf-lib is a JavaScript library.
This work has culminated in PDFApp, a dedicated Apps Script library created by Kanshi Tanaike based on the pdf-lib, but optimised for Apps Script. The source post includes a number of recipes for handling PDF files listed below:
Imagine you’re managing a Google Group, where important attachments are regularly sent. Manually saving these attachments to your Google Drive can be time-consuming and prone to errors. Is there a way to automate this process and ensure that you don’t miss any crucial documents?
It’s quite easy to tie yourself in knots when it comes to scheduling tasks in Google Apps Script to handle data created since the last run. This Medium post from Pablo Pallocchi shows a nice way your can structure Gmail searches using the after: operator combined with a ‘last execution date’ stored in Apps Script Properties Service. The result is a nice solution design to backup attachments sent to a Google Group to Google Drive. There’s lots of scope for extending and/or modifying this solution. All the details are in the source post.
