AppsScriptPulse

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

Efficiently exporting mulitple Google Docs files in PDF format with batch requests and Google Apps Script

This is a sample script for exporting Google Docs files (Spreadsheets, Documents, and so on) in PDF format with batch requests using Google Apps Script.

As a reference sample situation, in order to export 100 Google Document files as PDF files, when I tested this sample script, the processing time was about 150 seconds and no error occurred. And, I confirmed that 100 valid PDF files were created in my Google Drive.

Kanshi Tanaike has been busy again, this time looking at how you can handle batch exports from Google Docs, Sheets and Slides using Google Apps Script. There is quite a bit of engineering to get your head around but if you are looking for a copy/paste solution everything is well commented for you to drop this code into your own project. If you’d like more context about the solution there is a related post on Stack Overflow.

Source: Exporting Google Docs Files in PDF format with Batch Requests using Google Apps Script

FilesApp is a GAS library for retrieving file and folder list in Google Drive

When I create some applications using Google Drive, there are often the case which is required to retrieve the file list and folder list. I had prepared the script each time for each case so far. But recently, I thought that if there is a library for retrieving the file and folder list (as a tree), it will be useful for me and other developers.

The FilesApp library by Kanshi Tanaike has been around since 2018. I have to confess I wasn’t aware of this library until I read a more recent post, Retrieving Total File Sizes in Specific Folder of Google Drive using Google Apps Script. Similar to my own post on Creating a Google Drive report in Google Sheets the FilesApp library directly communicates to the Drive API, this library however goes a step further in terms of performance by using UrlFetchApp.fetchAll to handle some asynchronous Drive API calls.

Source: GAS Library – FilesApp

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

Opening and closing Google Forms on time trigger using Google Apps Script

This is a sample script for opening and closing Google Forms on time using Google Apps Script.

Here’s a handy little snippet if you would like to programmatically open/close one of your Google Forms to responses for specific hours of the day. The script includes another trigger that will repeat opening/closing the Google Form for responses each day. As this snippet uses .timeBased().at(date)  it’s easy for you to modify if you want to only have the form open to responses between two specific dates/times.

Source: Opening and Closing Google Forms on Time using Google Apps Script

Google Apps Script trends in Stack Overflow for 2022

From the above results, it can say that the current Google Apps Script has still been useful for a lot of users. But, the number of questions for one answerer is increased proportionally to the square of the year. Furthermore, the ratio of the solved questions for the total questions in 2022 is lower than that of 2021. So, in order to improve these issues, it is considered that growing answerers will be one of the important factors for the selectivity of Google Apps Script by users.

Kanshi Tanaike has recently published the 5th annual review of Google Apps Script tagged questions and answers on Stack Overflow [google-apps-script] to include data from 2022. This type of data always has to be read with caution. For example, whilst the total number of questions has again declined this year it could be argued this is because there is now a much bigger published knowledge base of both official and community resources. Declines in the number of people answering questions is more concerning. If you are someone who contributes to Stack Overflow a big thank you!

Source: Trend of google-apps-script Tag on Stackoverflow 2023

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

Report: Implementing a pseudo 2FA for Web Apps using Google Apps Script

In Google Apps Script, there is the Web Apps. When Web Apps is used, the users can execute Google Apps Script using HTML and Javascript. This can be applied to various applications. When the Web Apps is deployed with “Anyone”, anyone can access the Web Apps. And, there is the case that Web Apps deployed with “Anyone” is required to be used. Under this condition, when 2 Factor Authentication (2FA) can be implemented, it is considered that the security can be higher and it leads to giving various directions for the applications using Web Apps. In this report, I would like to introduce the method for implementing the pseud 2FA for Web Apps deployed with “Anyone” using Google Apps Script.

There can be scenarios where you’d like to publish an Apps Script web app with ‘anyone can access’, but still provide a level of security. Here’s a nice example from Kanshi Tanaike where they use MailApp to email the user a a time limited random passcode.

Magic links and passwordless login are used by a number of services, including Slack, and rather than including a password, as demonstrated in this example, it wouldn’t take much to turn this solution into a passwordless app. There are risks associated with email based authentication and if an attacker already has access to your email so other solutions are worth considering depending on the sensitivity of your web app.

Source: Report: Implementing Pseudo 2FA for Web Apps utanaikech.github.iosing Google Apps Script

Workaround: Checking Existence of File ID in Google Drive without Access token and API key using Google Apps Script

This is a workaround for checking the existence of file ID in Google Drive without both the access token and API key.

When you want to check whether the file of the file ID is existing in Google Drive, generally, you might use Drive API and Drive service (DriveApp) of Google Apps Script. In this case, the scope of Drive API is required to be used. By this, the access token and the API key (in the case of publicly shared files) are required to be used. But, there might be a case that the available scopes are limited. In this post, I would like to introduce a workaround for checking the existence of file ID in Google Drive without both the access token and API key.

This workaround could have been a nice addition to a recent project I was working on to audit a bunch of Google Drive file IDs. As noted in this post a big benefit of the approach is there is no need to include Google Drive authentication scopes to your project. See the source post for the code and explanation.

Source: Workaround: Checking Existence of File ID in Google Drive without Access token and API key

Efficient file management using batch requests with Google Apps Script 

Google Drive alone can handle small file management jobs, but for larger batches of files, it can be too much for a simple Drive script to manage. With Google Apps Script, even large batches can be executed within 6 minutes, offering businesses the monetary and time benefits of efficient file management. This report looks at how Google Apps Script improves file management with batch requests, judging its efficacy by measuring the benchmark.

We’ve regularly highlighted work from Kanshi Tanaike in Pulse and it’s nice to see it also being highlighted in the official Google Cloud Blog. I’m sure many Google Workspace developers, like me, have encountered issues with managing large volumes of Google Drive files. In the post Kanshi Tanaike highlights how batch methods can be used to greatly speed up the process when interacting with the Google Drive API.

Source: Efficient File Management using Batch Requests with Google Apps Script | Google Cloud Blog