Use Google Apps Script to automate email-based expense tracking. Store and track your receipts entirely through Gmail, Drive and Sheets
Here’s a nice tutorial on how to create an email-based expense tracking system using Google Apps Script. The solution allows users to submit expense reports via email, which are then automatically processed and stored in a Google Sheet with attachments stored in Google Drive.
The blog post by Joshua Mustill provides detailed instructions on how to set up the system, including how to create the Gmail labels and filters, the Google Sheet and the Apps Script code. There are some nice features in the code you might want to use in other projects including the creation of date based Google Drive folders for storing Gmail attachments.
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.
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
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 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.
