AppsScriptPulse

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

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

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

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

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

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

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

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

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

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

Quick backup solution for Google Apps Script by creating versions in Google Docs

Get security and peace of mind by backing up your Google Apps Script files to Google Docs and trigger for continuous backup.

We’ve featured a couple of methods for restoring Google Apps Script code in Pulse, but most of these rely on recovering a deployed version of the script. Stéphane Giron wields his Apps Script hammer and skilfully demonstrates how you can use Google Docs and it’s built-in version history as a way of keeping a backup copy of your code.

Source: Quick and not so Dirty backup solution for Google Apps Script Code

Three pitfalls to avoid when using the onEdit trigger in Google Apps Script

Illustration by ahmiruddinhidayat111198 on freepik.com https://www.freepik.com/author/fahmiruddinhidayat111198

  1. Making a Single Function Do Everything
  2. Expecting onEdit to Catch All Changes by Default

Source: Three Pitfalls to Avoid When Using the onEdit Trigger in Google Apps Script

Fix Google Apps Script file order problems with Exports 

How to fix Apps Script file loading order and defintion visibility problems with an Exports object.

It’s good practice to keep class and namespace definitions in separate files and avoid defining functions or variables in the global space. However, App Script doesn’t give you control over the order in which it loads files. If you reference a class or a namespace from one script file, it may not yet be defined. This is where an Exports object comes in.

As your script projects get larger and you start splitting out across script files you may find you need a little more structure. Class and namespace definitions are a good way to structure your code. Even when you do this you can still encounter problems with parts of your script trying to run before they are fully loaded.

This was a particular issue when the V8 runtime launched in 2020. This was fixed in June 2022, but it can still be an issue depending on how declarations are made in your code. To find out more about why this happens and how to fix it this post by Bruce Mcpherson shows how an Exports object can be used to structure your code.

Source: Fix Apps Script file order problems with Exports – Desktop Liberation

Using JWT as a license key in Google Apps Script Google Workspace Add-ons

Image credit: Zzapps

Many developers want to offer specific, additional functionality for specific users. One very common scenario is to differenciate in users that pay for this functionality.

When you deliver an add-on, you want to know whether this user has this special access. We know this as a license.

In this blog, I will show you how we can let the add-on know what the user’s license is. We are going to do this by giving the user a key, where they can ‘unlock’ functionality by pasting this in their add-on.

Here’s an elegant solution from Riël Notermans (Zzapps) for letting users unlock premium functionality in your Workspace Add-on without relying on an external fetch or database by using an encrypted JWT web token.

You can include various data in the token and this example a subscription email and expiry is included which can be checked locally, for example, comparing the email from Session.getActiveUser().getEmail().

Source: Use JWT as a license key in Google Apps Script add-ons

Bulk combine CSV files into a single Google Sheet with Google Apps Script

Bulk combine multiple CSV files into a single Google Sheet, putting the data in specific locations and creating multiple tabs.

Bulk combine CSV files into a single Google Sheet

Bulk combine CSV files into a single Google Sheet

The following Google Apps Script is designed to take a number of CSV files (structured in an identical way) stored in a Google Drive folder and combine them all into a single Google Sheet file. It maps the data within the CSV file to specific cells in the Google Sheet, along with creating a tab for each of the CSV files to separate them out.

Source: The Gift of Script: Bulk combine CSV files into a single Google Sheet