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.
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.
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
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"
]
}
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
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.
ChatGPT is pretty much a universal API: one endpoint to get any data.
ChatGPT has recently taken the world by storm. Unless you have been living under a rock, then you have certainly seen a variety of content on this topic. One of its greatest advantages is its ease of use, and it is perfectly usable in Google Apps Script. With that in mind, here is one use case that we can implement: generating fake 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.
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.
Illustration by ahmiruddinhidayat111198 on freepik.com https://www.freepik.com/author/fahmiruddinhidayat111198
The onEdit trigger is likely the most used trigger in Google Apps Script. It runs automatically with an event object whenever you change a value in a spreadsheet — programmatic changes excluded — thus allowing you to execute a script based on context. When done properly, it can be extremely powerful. When done wrong, however, it can feel unreliable and messy.
In this article, we will learn to avoid three common pitfalls:
Not Exiting Early
Making a Single Function Do Everything
Expecting onEdit to Catch All Changes by Default
For this purpose, we will build a simple script to handle a task list in Google Sheets. It will do two things: add a checkbox next to new tasks and add a completion date when each task is checked as done.
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().
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
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.
