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.
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.
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.
