AppsScriptPulse

Add-on, Solutions, Intermediate

SpeedStore: Blazingly fast Properties storage for Google Apps Script ⚡

Tags:
| | 0 Comments |

Retrieving and saving properties in Google Apps Script can be slow, especially if there are a lot of them. SpeedStore is a blazingly fast in memory properties store which you can use to make retrieving and saving properties much easier.

Continuing yesterday’s theme highlighting some of the components for developing a Google Workspace Add-on where John McGowan highlighted  how Properties Service can be used to store and use a “licence” property to customise your add-on UI, we continue by looking at how you can handle property storage.

There have been a number of community contributions in this area such as Bruce Mcpherson’s bmCrusher. Another option is SpeedStore from Joshua Snyder. Not as feature filled as bmCrusher but the benefit is the library is more compact. SpeedStore still comes with some very useful features including automatically handling properties over 9kb and JSON encoding/decoding. Perhaps the biggest benefit is speed particularly when you are using a single store for all your properties.

Source: GitHub – joshsny/SpeedStore: Blazingly fast Properties storage for Google Apps Script ⚡

Add-on, Solutions, Beginner

Differentiating Google Workspace Add-on sidebar features based on a user licence property in Google Apps Script

Tags:
| | 0 Comments |

I always get requests as to how people can add a license to their Google Add-on. There are a few different steps and here I will show how you can share different information in the Sidebar based on a license status.

In Pulse we’ve previously featured a couple of community contributions on how to monetize your Google Workspace Add-On. Corentin Brossault’s How to monetize your Google Workspace add-on? provides some great code snippets and tips for handling user authentication and payment. We’ve also featured Riël Noterman’s solution for Using JWT as a license key in Google Apps Script Google Workspace Add-ons. This related  post from John McGowan (Automagical Apps) provides another piece to the puzzle demonstrating how you can use templates in HTMLService to switch user messaging based on stored user properties.

Source: Differentiating Google Addon Sidebar features based on a license | Automagical Apps Blog

Comment, Official News, AppSheet, No Code

Announcing: AppSheet Chat Apps now available to preview program!

Tags:
| | 0 Comments |

We’re happy to announce that AppSheet-powered no-code chat apps are now available in preview for Workspace customers in AppSheet!

You can access this feature right now if you are in the Preview Program, and you can provide any feedback in this thread. If needed, additional information can be found in our help center articles. We’re targeting a full launch for later this year.

I’ve been spending a fair bit of time in Google’s ‘no-code’ platform AppSheet. Even for developers there is plenty to get your teeth into with features like Apps Script integration. One of the incredibly powerful core features of AppSheet is the ability to send dynamic emails which include forms designed in AppSheet. These allow users to interact with your app without leaving their inbox.

Whilst the inbox dominates in many business sectors, support for frontline workers increasingly leans on other communication channels including Google Chat. With the announcement of AppSheet powered no-code Google Chat apps there is now an opportunity to also push dynamic forms, send data and interact with Chat users in that space. For developers there is also the opportunity to do even more with the Apps Script integration. Follow the link to the announcement post to find out more…

Source: Announcing: AppSheet Chat Apps Now Available to Preview Program!

Snippet, Beginner, Maps

Validate postal address with the new Google Maps Address Validation API and Google Apps Script

Tags:
| | 0 Comments |

 

Validate addresses using Google Maps Address Validation and Apps Script. Discover onleeaddress the add-on for Google Workspace.

I missed the announcement in November 2022 that there is a new Google Maps Address Validation API. I also missed this post from Stéphane Giron showing how you can use the Address Validation API in Google Apps Script.

The concept is simple, you provide the Address Validation API with what you think is a correct address and the API returns information on each component of the address and additional metadata. Visit the source post for more details on setup and some example code.

Source: Validate postal address with the new Google Maps Address Validation API and Apps Script

Snippet, Solutions, Sheets, Beginner

Find all matching values in Google Sheets cells with Google Apps Script

Tags:
| | 2 Comments |

Learn how to search and find all values in an entire Google Sheet spreadsheet, in a specific sheet tab or in a range with Apps Script.

Handy little snippet from Scott Donald on using the Google Apps Script TextFinder class to find all the occurrences of matching text in a Google Sheet. The bit I particularly liked in this solution was how Scott used a .map method to return an object array of cell hits including A1 notation:

Array containing an object for each sheet containing the sheet name and the cell location (Image credit: Scott Donald)

Follow the source link for more information including a video tutorial.

Source: Find All Values in Google Sheets with Apps Script – Yagisanatode

Snippet, Solutions, Sheets, V8, Intermediate

Hiding/deleting non-consecutive rows and columns in Google Sheets using Google Apps Script

Tags:
| | 0 Comments |

These are the sample scripts for hiding and deleting rows and columns on Google Spreadsheet [sic] using Google Apps Script. I sometimes see the questions for hiding and deleting rows and columns on Spreadsheet at Stackoverflow. So here, I would like to introduce the sample scripts for this.

In SpreadsheetApp there is the .deleteRow() and .deleteRows(), but when you have a lot of data or non-consecutive rows it can really eat into your Apps Script runtime limit. In searching for a better way of doing this I found this great solution from Kanshi Tanaike on Stack Overflow which uses the Google Sheets Advanced Service to delete multiple rows using one API call!

Using Sheets.Spreadsheets.batchUpdate isn’t just limited to deleting rows and for more examples see the source post which includes examples for hiding and deleting both rows and columns.

Source: Hiding and Deleting Rows and Columns on Google Spreadsheet using Google Apps Script

Snippet, Beginner, Drive

Organise Google Drive files into a JavaScript object for use with Google Apps Script

Tags:
| | 0 Comments |

Organise Google Drive files into a JavaScript Object so that they can be looped through for each named individual.

Files to loop through and organise

Files to loop through and organise

The following Google Apps Script is designed to go through a folder of Google Drive files where a number of tutors have individual files for groups of students they teach. The code will collate all of the relevant files (specifically their IDs) that belong to each tutor, so that at a later date we can loop through the organised data set and create individual Google Sheets for each tutor and compile their student data into it.

What this code ultimately allows us to achieve is the ability to go through the files and get them organised for further coding.

In this example we have a file name pattern of ModuleCode – Tutor Name – Group Number for example ABC – Jane Doe – Grp 02. So we want to collate all of the files that belong to Jane Doe first, then move on to the next tutor (Micky Mouse in this example).

The collated data will look like this:

{
   "Jane Doe":[
      "FILE ID HERE",
      "FILE ID HERE",
      "FILE ID HERE"
   ],
   "Micky Mouse":[
      "FILE ID HERE",
      "FILE ID HERE"
   ]
}

Source: The Gift of Script: Organise files into a JavaScript Object

Solutions, Drive, Sheets, Beginner

Bulk add files to existing Google Drive folders using Google Apps Script

Tags:
| | 0 Comments |

Take a Google/Shared Drive folder full of files that you want to make a copy of and place into another set of existing Drive folders.

Bulk add files to existing Google Drive folders

Bulk add files to existing Google Drive folders

The following Google Apps Script tool is designed to take a Google/Shared Drive folder full of files that you want to make a copy of and place into another set of existing Drive folders.You can decide if you want the subfolder name appending/prepending/ignoring in the file name of the new copy to help make it unique.

The tool will iterate through a single level of folders (not sub-subfolders) and for each one place a copy of the files into it.

Source: The Gift of Script: Bulk add files to existing Google Drive folders

Solutions, Sheets, V8, Intermediate

Using ChatGPT to Generate Fake Data in Your Spreadsheet with Google Apps Script

Tags:
| | 0 Comments |

ChatGPT is pretty much a universal API: one endpoint to get any data.

Fake data is useful for testing purposes. At times, real data is too sensitive, and sometimes you do not have access to real data before production, but you need to begin working with something. Here is where fake data comes in.

We used to have a great library for fake data called Faker.js; however, Marak, the author, became so frustrated that he could not monetize the solution that he pushed an update that broke everything, which led to him being blocked on GitHub and NPM. As a result, the library is no longer maintained. ChatGPT, however, is not going anywhere.

Source: Using ChatGPT to Generate Fake Data in Your Spreadsheet with Google Apps Script

Snippet, Solutions, Drive, V8, Intermediate

Merging multiple PDF files as a single PDF and converting all the pages in a PDF to PNG images using Google Apps Script

Tags:
| | 0 Comments |

This is a sample script for merging multiple PDF files as a single PDF file using Google Apps Script. [and] This is a sample script for converting all pages in a PDF file to PNG images using Google Apps Script.

Kanshi Tanaike has recently been exploring and sharing some Apps Script solutions for handling PDF Documents using the PDF-LIB JavaScript library. So far they have looked at merging PDF files as well as converting PDF pages into PNG images.

The solution uses fetch and eval to load PDF-LIB, but with a minor modification hoisting the declaration of setTimeout you can also copy the source code into the script editor and avoid the evils of evals (a modified example here).

The Apps Script execution runtime limit will be a factor in the size of PDF Documents you can handle, but for smaller jobs a great solution to keep in mind.

Source: Merging Multiple PDF Files as a Single PDF File using Google Apps Script and Converting All Pages in PDF File to PNG Images using Google Apps Script