AppsScriptPulse

Automate invoice management in Gmail with Document AI and Google Apps Script

Image credit: Full diagram of invoice automation in Gmail with Document AI and Apps Script –
Stéphane Giron

Tired of manually managing your invoices in Gmail? Say goodbye to the hassle and hello to efficiency with Document AI and Apps Script.

There is a lot of buzz around generative AI, but it’s also worth remembering Google have a well established service offer in other forms of AI, the Document AI being one example of this. This post from Stéphane Giron highlight an Apps Script based workflow for analysing invoices attached to Gmail messages using the Document AI. There is a bit to setup in a Cloud project to get this working and as a paid for service costs to consider. Further information on this and all the code you need to get started are included in the source post.

Source: Automate invoice management in Gmail with Document AI and Google Apps Script

Google Apps Script Patterns: Keeping a gam generated users report up-to-date with Google Apps Script

In Google Workspace gam is probably the ‘go to’ tool command line tool which allows administrators to easily manage domain and user settings. Recently I was asked about how you can keep gam generated reports up-to-date using Google Apps Script. It’s worth remember that gam uses public Google Workspace APIs when it performs actions and reports. In this post I show you a pattern for building script to keep gam reports fresh.

Source: Google Apps Script Patterns: Keeping a gam generated users report up-to-date with Google Apps Script

Performance report for CacheService versus SpreadsheetApp for read/write in Google Apps Script

Image credit: Ignacio Lopezosa Serrano

For read-heavy applications that don’t involve components external to Apps Script accessing the cache and don’t exceed the CacheService limits, use CacheService. For write-heavy applications or for when some external parts require access to the same cache, use Spreadsheet App.

An interesting report from Ignacio Lopezosa Serrano on the relative performance of CacheService and SpreadsheetApp for reading/writing data with Google Apps Script. Some surprising results and something I think to be kept in mind is how these tests compare to ‘real world’ conditions. As also pointed out in the post there are some service limitations of CacheService to keep in mind particularly around storage size limits.

Source: The use of Caches in Google Apps Script

Rearranging column order in Google Sheets using Google Apps Script

This is a sample script for rearranging columns on Google Spreadsheet using Google Apps Script.

Following on from the recent example from Scott Donald on How to sort tabs in Google Sheets with Google Apps Script, here is a handy little snippet from Kanshi Tanaike for changing the column order in Google Sheets using Apps Script and the moveColumns method.

Source: Rearranging Columns on Google Spreadsheet using Google Apps Script

How to sort tabs in Google Sheets with Google Apps Script

 

Learn how to sort Google Sheet tabs in ascending and descending order with Google Apps Script Magic. Code and video inside!

Latest from Scott Donald, this time Scott is looking at Google Sheets tab sorting with Google Apps Script. Hepefully something for everyone in this post and for me it was learning about JavaScript’s Intl.Collator() , which allows language sensitive string comparisons.

Source: How to Sort Tabs in Google Sheets with Google Apps Script – Yagisanatode

🎥 🏉 AppSheet + Apps Script for sports videography highlights tracking

Image credit: Zack Akil

During the summer I was asked to help with some video recording for a rugby tournament, but rather than spending hours rewatching footage to find highlights, I quickly threw together some tech to make things a lot easier.

A slick solution that uses a AppSheet app for data collection with Apps Script to cue up video files hosted on Google Drive. The Apps Script code is hosted on GitHub if you’d like to take a peak. Hopefully this example gives you inspiration for your own little projects.

Source: 🎥 🏉 AppSheet + AppScript for sports videography highlights tracking

Creating a Google Drive report in Google Sheets: Making Google Workspace Enterprise solutions with Google Apps Script

Image credit: Martin Hawksey (with the help of DALL·E 2)

Think 10x — supercharging your Google Apps Script solutions by directly calling Google Workspace Enterprise APIs.

The Google Apps Script built-in services like SpreadsheetApp, Maps and GmailApp are a great onramp for users with limited coding experience, the flip side is you can find yourself easily getting results but not in the most efficient way. DriveApp is a great example where Google have made it easy to iterate across folders and files, but when you have lots of folders and files it becomes a time consuming process and you hit execution limits.

An alternative approach is using Google Apps Script to make direct calls to the Drive API. The benefits of this approach is you can be more specific in the data you want back and it gives more flexibility with how you call the API, in some cases with the ability to make batch or asynchronous processes.

In this post I highlight a method ideal for scenarios when you want to index larger volumes of My Drive files and folders to a Google Sheet with calls directly to the Drive API. The post includes some sample code you can use which instead is able to reduce a 4 minute runtime to index 10,000 files and folders to one that can complete in under 40 seconds!

Source: Creating a Google Drive report in Google Sheets: Making Google Workspace Enterprise solutions with Google Apps Script

Putting values from of all Google Sheets in Google Drive folder into a master Sheet with low process cost using Google Apps Script

 

This is a sample script for putting the values of all Spreadsheets in a folder to the master Spreadsheet with a low process cost using Google Apps Script.

Some clever scripting from Kanshi Tanaike to combine individual Google Sheets in a Google Drive folder into a single master sheet. To achieve this the solution uses the Google Sheets Advanced Service combined with UrlFetchApp.fetchAll() to asynchronous process the source Google Sheets. It’s reported that this method was able to process 50 source sheets in 10 seconds!!! There are some limitations to be aware of highlighted in the source post, but for a method to quickly process a lot of data this solution is definitely one to keep in mind.

Source: Putting Values of All Spreadsheets in Folder to Master Spreadsheet with Low Process cost using Google Apps Script

How to combine arrays of data into one Google Sheet with Google Apps Script

In this post, we’re going to look at how we can easily combine arrays of data on separate sheets in a Google Sheet and add them on one single sheet. We’re going to use the relatively new spread operator (…) to do this.

A little knowledge about data manipulation can get you a long way, particularly if you are doing lots of work in Google Sheets. This post from Baz Roberts is an opportunity to learn about spread syntax (also known as the spread operator) in arrays. Have a read and hopefully you’ll see the benefits.

Source: How to combine arrays of data into one sheet – this post is also available to read on bazroberts.com

11 new analytical functions in Google Sheets for 2023

Get the lowdown on the 11 new analytical functions introduced to Google Sheets in 2023, including LET and array manipulation functions.

For developers Google Sheets is becoming an increasingly rich playground. Features like the SQL like QUERY function have enabled users to do more with their data. More recently Google have released a range of new functions which I’m sure many developers and power users will be able to relate to. New functions like EPOCHTODATE , LET as well as other recent additions like MAP and REDUCE will be familiar concepts to many developers. In this post from Ben Collins the latest Google Sheet functions are reviewed.

Source: 11 New Analytical Functions In Google Sheets For 2023