Skip to content
/ taking-advantage-of-google-apps-script Public

Here, CLI tools, libraries, Add-ons, Reports, Benchmarks and Sample Scripts for taking advantage of Google Apps Script which are publishing in my blog, Gists and GitHub are summarized.

License

MIT license
363 stars 36 forks Branches Tags Activity
Star
Notifications You must be signed in to change notification settings

tanaikech/taking-advantage-of-google-apps-script

BranchesTags

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

524 Commits
Japanese_version
Japanese_version
 
 
LICENCE
LICENCE
 
 
README.md
README.md
 
 

Repository files navigation

Taking Advantage of Google Apps Script (Tanaike's list)

Here, CLI tools, libraries, Reports, Benchmarks and Sample Scripts for taking advantage of Google Apps Script which are publishing in my blog, Gists, GitHub and my answers on Stackoverflow are summarized. I hope that you have the chance for knowing the possibilities of Google Apps Script from my contents. If these are useful for you, I'm glad.

Japanese version of this list is here.


Index



News


Trend of Google Apps Script

At Stackoverflow, a lot of people post questions and answers to the questions every day. There are various tags in Stackoverflow. A lot of discussions are performed at each tag. Their discussions bring important information and are much useful for a lot of people. As one of the tags, there is "google-apps-script". I sometimes discuss the questions with that tag. When we see the discussions, we can notice that the discussions are changed and progressed over time, because "Google Apps Script" which is the origin of the tag is updated. This report thinks this change is the trend of the tag of "google-apps-script". This trend includes the number of questions, questioners, answerers, and tags added to the tag of "google-apps-script". The trend of the tag of "google-apps-script" is deeply related to the progression of Google Apps Script and the various applications for Google Apps Script. In this report, as one of several approaches, the trend of Google Apps Script was investigated by statistically analyzing all questions with the tag of "google-apps-script". As the result, it was found that by investigating all questions with the tag "google-apps-script", the tags added to this main tag strongly affected the trend of the tag "google-apps-script". Also, the possibility which can estimate the future trend by investigating the tags added to the tag of "google-apps-script" was indicated.


Settings


CLI tools for GAS

  • ggsrun : Execute Google Apps Script (GAS) at own terminal on local PC.
  • gislack : Submit files to both Gist and Slack.
  • goris : Search for images with Google Reverse Image Search (goris).
  • gogauth : Easily retrieve access token for using APIs on Google.
  • gorearrange : Interactively rearrange a text data on a terminal.
  • goodls : Download shared files from Google Drive.

Web Applications


GAS libraries

You can search the GAS libraries at Search Google Apps Script Libraries

  • BatchRequest : This is a library for running Batch Requests using Google Apps Script (GAS).
  • ConvertNFDtoNFC : This is a script for converting strings from NFD (Normalization Form Decomposition) to NFC (Normalization Form Composition) using Google Apps Script.
  • FilesApp : FilesApp is a GAS library for retrieving file and folder list in Google Drive using Google Apps Script (GAS). Also this can create a tree from all files and folders in Google Drive.
  • ImgApp : This is a library of image tools for Google Apps Script.
  • ManifestsApp : This is a Manifests library for Google Apps Scripts.
  • ProjectApp : This is a project library for Google Apps Script (GAS).
  • ProjectApp2 : This is a GAS project library for Google Apps Script (GAS). This library can be used for the projects of both standalone script type and container-bound script type.
  • OnedriveApp : This is a library of Google Apps Script for using Microsoft OneDrive.
  • Resumable_Upload_For_WebApps : This is a sample script for uploading files with large size (> 50 MB) at Web Apps using Google Apps Script (GAS). The resumable upload method is used for uploading files. This script can be also applied to the script using gapi of javascript.
  • RunAll : This is a library for running the concurrent processing using only native Google Apps Script (GAS).
  • SOUWA_GAS : GAS library for summing string elements in an array at the high speed
  • ZipFolder : This is a library for zipping a folder using Google Apps Scripts.
  • RangeListApp : RangeListApp is a GAS library for retrieving, putting and replacing values for Spreadsheet by a range list with a1Notation using Google Apps Script (GAS).
  • DownloadLargeFilesByUrl : DownloadLargeFilesByUrl is a GAS library for downloading large files from URL to Google Drive using Google Apps Script (GAS).
  • ArrangeStackingOrder : ArrangeStackingOrder is a GAS library for arranging the stacking order of page elements on Google Slides using Google Apps Script (GAS).
  • ProcessApp : This is a library for retrieving the process and information of Google Apps Script. For example, one of methods retrieves the total execution time of all functions executed by the time-driven trigger at owner's account.
  • GistChecker : This is a GAS library for notifying the change of number of comments, stars and forks of own Gists as an email using Google Apps Script.
  • FetchApp : This is a GAS library for creating and requesting the type of multipart/form-data using Google Apps Script. This library enhances Class UelFetchApp of Google Apps Script.
  • GetEditType : GetEditType is a GAS library for retrieving the edit types of the OnEdit event trigger of Spreadsheet using Google Apps Script (GAS).
  • UnzipGs : This is a GAS library for unzipping a Zip file protected by a password using Google Apps Script.
  • GmailToList : This is a library for exporting all messages of Gmail as a list using Google Apps Script (GAS).
  • EncodeApp : EncodeApp is a GAS library for retrieving the encoding set (charset) and doing URL encode with the specific encoding set using Google Apps Script (GAS).
  • DateFinder : DateFinder is a GAS library for searching the date objects from the cell range on the sheet in the Spreadsheet and retrieving the searched range as the RangeList object using Google Apps Script (GAS).
  • RichTextApp : This is a GAS library for copying the rich text with the text styles from Google Document to Google Spreadsheet or from Google Spreadsheet to Google Document using Google Apps Script (GAS). And, also the rich texts in the cells can be converted to HTML format.
  • GPhotoApp : This is a GAS library for retrieving and creating the albums and media items using Google Photo API using Google Apps Script (GAS).
  • CopyFolder : This is Google Apps Script library for copying folder on Google Drive.
  • OwnershipTransfer : This is a Google Apps Script library for achieving the ownership-transfer of the specific folder including the files and sub-folders using Drive API.
  • GASProjectApp : This is a Google Apps Script library for creating, updating and exporting Google Apps Script project of the standalone type using Drive API. In this case, Apps Script API is not used.
  • DocsServiceApp : This is a Google Apps Script library for supporting Document service, Docs API, Spreadsheet service, Sheets API, Slides service and Slides API. The aim of this library is to compensate the processes that they services cannot achieve.
  • HtmlFormApp : This is a Google Apps Script library for parsing the form object from HTML form and appending the submitted values to the Spreadsheet.
  • DocNamedRangeApp : This is a Google Apps Script library for managing the named range on Google Documents.
  • RichTextAssistant : This is a GAS library for supporting editing RichText in Google Spreadsheet using Google Apps Script.
  • UtlApp : This is a Google Apps Script library including useful scripts for supporting to development of applications by Google Apps Script.
  • TemplateApp : This is a Google Apps Script library for easily managing the template of Google Documents and Google Slides using Google Spreadsheet as a database using Google Apps Script.
  • TriggerApp : This is a Google Apps Script library for efficiently managing the time-driven triggers for executing Google Apps Script using Google Apps Script.
  • PDFApp : This is a Google Apps Script library for managing PDFs.
  • ScriptHistoryApp : This is a Google Apps Script library for managing the histories of the Google Apps Script project.
  • MicrosoftDocsApp : This is a Google Apps Script library for using Microsoft Docs files (Word, Excel, and PowerPoint files) using Document service, Spreadsheet service, and Slides Service of Google Apps Script.
  • CorporaApp : This is a Google Apps Script library for managing the corpora of Gemini API.
  • ImageBotApp : This is the image bot using Gemini with Google Apps Script and Google Drive.
  • GoogleApiApp : This is a Google Apps Script library for supporting to use Google APIs with Google Apps Script.
  • GeminiWithFiles : A new Google Apps Script library called GeminiWithFiles simplifies using Gemini, a large language model, to process unstructured data like images and PDFs. GeminiWithFiles can upload files, generate content, and create descriptions from multiple images at once. This significantly reduces workload and expands possibilities for using Gemini.
  • MoveFolder : This is a Google Apps Script library for moving a folder including files and folders on Google Drive.

GAS library database


Go libraries

  • go-getfilelist : This is a Golang library to retrieve the file list with the folder tree from the specific folder of Google Drive.
  • go-gettokenbyserviceaccount : This is a Golang library to retrieve access token from Service Account of Google without using Google's OAuth2 package.
  • go-gdoctableapp : This is a Golang library for managing tables on Google Document using Google Docs API.

Node.js modules

  • node-getfilelist : This is a Node.js module to retrieve the file list with the folder tree from the specific folder of Google Drive.
  • node-gdoctableapp : This is a Node.js module to manage the tables on Google Document using Google Docs API.

Python library

  • getfilelistpy : This is a python library to retrieve the file list with the folder tree from the specific folder of Google Drive.
  • gdoctableapppy : This is a python library to manage the tables on Google Document using Google Docs API.

Javascript library


Add-ons

  • RearrangeScript : Rearranging Google Apps Scripts (GAS) in a project which can be seen at the script editor. GitHub
  • ShapeApp : Manipulating shapes on Google Slide. It can create and update shapes by inputting parameters, and can arrange shapes. This is made of Google Apps Scripts (GAS). GitHub

Reports

Improved Algorithms for Summation of Array

I considered an efficient algorithm for summation of array elements. All elements in an array are string. When those elements are summed using scripts, a standard method is to add each element in order. If the script is run without any optimize, the process becomes gradually sluggish, because the total amount of active data during the summation process is proportional to the square of the number of array elements. This leads directly to the high process-cost. Such phenomenon notably appears at Google Apps Script (GAS). This report says about the solution of this problem using a new algorithm of a pyramid method. The pyramid method achieves that the total amount of active data increases proportional to the linear of the number of array elements. By this, the processing time becomes much shorter than that of the process using the standard method. The pyramid method achieved the process-cost reduction of 99.7% compared with the standard method at GAS. I realized again that new discoveries are hidden into the familiar scenes of every-day life.

Taking Advantage of Manifests by GAS Library

By recent Google update (Google update at October 24, 2017), various new winds to GAS developers were blown. There is "Manifests" as one of the new winds. "Manifests" makes us manage the project using JSON. Especially, the special scopes which have to use OAuth2 process can be used by only setting them to the Manifests. I think that this is the largest modification. However, when scopes are added to a project using Manifests, users who use the project can use only added scopes. This means that when users create scripts in the project, if there are some scopes which is required to be added, such scopes cannot be automatically added. So the error of "Insufficient Permission" occurs. In this report, I would like to introduce the workaround for avoiding this problem.

Difference Between Given Values and Retrieved Values for Shapes on Google Slides

This is a document for explaining the difference between given values and retrieved values for shapes on Google Slides.

Taking advantage of Web Apps with Google Apps Script

There is Web Apps as one of applications using Google Apps Script (GAS). I sometimes use this Web Apps. But I have only a little the information for the specification of Web Apps. So in order to take more advantage of Web Apps, I investigated and summarized about this. The aim of this report is to become one of the basic information for creating various applications using Web Apps with GAS.

Limitation of Images for Inserting to Spreadsheet using Google Apps Script

Here I would like to introduce about the limitation of images for inserting to Spreadsheet using Google Apps Script (GAS). When you want to insert the images to Spreadsheet using GAS, insertImage() of class Sheet is usually used for this situation. At this time, an error sometimes occurs. This indicates that there is the limitation for inserting images to Spreadsheet. So I investigated the limitation. As a result, it was found that the limitation depends on the image area (pixels^2) rather than the file size of it. The maximum area of image which can be inserted was 1,048,576 pixels^2.

Asynchronous Processing using Event Triggers

This is a report about the possibility of asynchronous process using event triggers. onEdit() which is a simple trigger is often used as a trigger when the values are modified on Spreadsheet. When users want to use the script including some methods which are required to be authorized as the onEdit event, a installable trigger of onEdit is used. If the trigger is installed for the function of onEdit(), when the event trigger is run, onEdit() is run 2 times. In order to avoid this, the installable trigger is installed to the functions except for the functions of simple triggers. The functions of simple triggers which is the same events are not used in the project. When I thought about this situation, I thought that both onEdit() which is run by the simple trigger and the function which is run by the installable trigger might be able to be used, simultaneously. So I investigated about this situation. As the result, it was found that the following simple triggers and installable triggers work as the asynchronous process.

Limitation of Array.prototype.push.apply under V8 for Google Apps Script

When V8 is enabled, Array.apply has the limitation for the number of elements. When it is over the limitation, an error like RangeError: Maximum call stack size exceeded occurs, while the issue didn't occur when V8 is disabled. In this case, this issue occurs at both Google Apps Script and Javascript. So please be careful this.

When // in template literal is used in a HTML file in script editor, it is used as a comment start

When // in template literal is used in a HTML file in script editor, it is used as a comment start.

Characteristics of Response for onSelectionChange

I have already reported about "Change Tab Detection on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script". Ref It is considered that when the situation which uses the event trigger of onSelectionChange is thought, the response speed is important. So, here, I investigated the characteristics of response for the event trigger of onSelectionChange.

Detecting Quickly Checked Checkboxes on Google Spreadsheet using Google Apps Script

This is a report for detecting quickly checked checkboxes on Google Spreadsheet using Google Apps Script. It supposes that when the checkbox is checked, a function of Google Apps Script is run by the event trigger. In this case, when the multiple checkboxes on Google Spreadsheet are checked quickly, the script cannot be run for all checked checkboxes, because of the response speed of the event trigger. It is considered that to understand the response of event trigger is useful for creating the application for Spreadsheet. In this report, the detection of quickly checked checkboxes on Google Spreadsheet using Google Apps Script has been investigated. From this result, it led to understanding the response of event trigger.

Report: Processing to Create New File to Specific Folder using Drive API

In this report, I would like to report for processing to create new file to the specific folder using Drive API. When the new file is created to the specific folder using Drive API, the property of parents with the value of folder ID is included in the request body of the method "Files: create". About this process, I had thought that the file is directly created to the specific folder. But, I could confirm that the new file is created by the following process.

  1. Create new file to the root folder.
  2. Move the created file to the specific folder.

These process is done by one API call. In this report, I would like to introduce the experimental result for confirming above process. In this case, Drive API v3 is used with Google Apps Script.

Statistical Analysis of Duplicated Questions for google-apps-script tag in Stackoverflow

At Stackoverflow, a lot of people post the questions and answer to the questions every day. By this, there are a lot of important information in Stackoverflow. I have already reported "Trend of google-apps-script Tag on Stackoverflow" using the data retrieved from Stackoverflow. Ref It is found that the important statistical result can be obtained by analyzing the data on Stackoverflow. In this report, I would like to introduce the statistical analysis of duplicated questions for the google-apps-script tag in Stackoverflow. When the duplicated question is analyzed, it is considered that the important issues for users can be known. As the result, it was found that there are the trend that the duplicated questions related to Javascript, Google Spreadsheet, the process cost and the cooperation with HTML and Javascript are posted.

Safe-Uploading for Google Drive by HTML in External Server using Google Apps Script

This is a report for safe-uploading files to Google Drive by HTML put in the external server using Google Apps Script.

When you want to make the user upload a file to your own Google Drive using the HTML put in the external server of Google side, when the file size is smaller than 50 MB, this can be achieved without using the access token. Ref (When the HTML is put in the internal server of Google side, you can also use google.script.run.) But, when the file size is over 50 MB, it is required to upload the file with the resumable upload. In this case, the access token is required to be used. In this case that the user uploads to your own Google Drive, when the access token is used in the upload, it is considered that this is the weak point of the security. In this report, I would like to propose the method for safe-uploading files to Google Drive by HTML put in the external server using Google Apps Script. Please think of this as one of several methods.

Specification of Search Query for File List Method in Drive API

In this report, I would like to report about the current specification of the search query for the file list method in Drive API.

Recently, I noticed that the specification of the search query for the file list method in Drive API might have been changed. I thought that to know the change of specification of the search query is important for creating the application using Drive API. In this report, I would like to introduce the current specification of the search query.

Concurrent Writing to Google Spreadsheet using Form

When the users try to write to Spreadsheet using a form, the developers have to consider to the concurrent submission from the form. For example, when the multiple users submit the data with the form simultaneously, all data are possibly not to be saved to the Spreadsheet. So it is considered that it is important to know the information about the concurrent writing to Google Spreadsheet using a form. In this report, such situation was investigated.

As the result, when the success rate for writing concurrently to Google Spreadsheet is investigated, it was found that the concurrent writing with Google Form is suitable rather than Web Apps created by Google Apps Script. The threshold number of users for succeeding to write all data to Spreadsheet was 35 for Google Form and 26 for Web Apps, respectively. And, when Web Apps is used, it was also found that Lock Service was definitely required to be used for the multiple submission.

Large Decimal Numbers and Exponential Notation for Google Spreadsheet

In this report, it has investigated the large decimal numbers and the exponential notation for Google Spreadsheet. When the large decimal numbers are put to the Spreadsheet, the Spreadsheet automatically sets the display value using the exponential notation. In this report, the result when the values are retrieved by Spreadsheet service and Sheets API is shown.

Report: Images put with IMAGE function on Google Spreadsheet

This is a report about images put with "=IMAGE(IMAGE_URL)" function on Google Spreadsheet.

When "=IMAGE(IMAGE_URL)" is put to a cell "A1" on Spreadsheet, the image is shown in the cell. For this situation, when the cell "A1" is copied with "range.copyTo(range, { contentsOnly: true })", the formula is removed and an image can be seen. I thought that when the image of the URL is removed, how are those images. In this report, such a situation was investigated.

Occurring and Resolving Infinite Loop on Google Spreadsheet using Google Apps Script

Here, I would like to introduce a report for occurring and resolving the infinite loop on Google Spreadsheet using Google Apps Script. I have reported this to Google issue tracker.

Applicating Spread Syntax and Destructuring assignment to Google Spreadsheet with Google Apps Script

In this report, I would like to introduce to apply the spread syntax and the destructuring assignment to Google Spreadsheet with Google Apps Script. The destructuring assignment can be used without V8 runtime. But, the spread syntax is required to be used with V8 runtime. Recently, I often saw the script using them at Stackoverflow. And also, I have sometimes gotten the questions related to the spread syntax and the destructuring assignment. So, I thought that I would like to introduce in my blog.

Report: Handling 10,000,000 cells in Google Spreadsheet using Google Apps Script

On March 14, 2022, it reported about "Google Sheets doubles cell limit". Ref By this update, now, the users can use 10,000,000 cells in a Google Spreadsheet. This is great news for us. When I tried to handle 10,000,000 cells in a Google Spreadsheet using Google Apps Script, it was found that there were various important points. In this report, I would like to introduce the important points for handling 10,000,000 cells in Google Spreadsheet using Google Apps Script.

Report: Obtaining Values from GOOGLEFINANCE using Google Apps Script

This is a report for obtaining the values from GOOGLEFINANCE using Google Apps Script. When I tested to retrieve the values from GOOGLEFINANCE function on Google Spreadsheet using Google Apps Script, I noticed that the values can be retrieved.

Report: Publishing Various Google Docs with Same URL using Google Apps Script

This is a sample method for publishing various Google Docs files with the same URL using Google Apps Script.

By updating on May 25, 2022, the content got to be able to be embedded as a full page in the new Google site. Ref In this method, this is used.

Report: Recent Value of ScriptApp.getService().getUrl() in Google Apps Script

Report: Documentation Comments including JsDoc for Functions of Google Apps Script

This is a report for the documentation comments for the functions of Google Apps Script.

When the documentation comments for functions of Google Apps Script are considered, you will think JsDoc. At Google Apps Script, a part of JsDoc can be used. But, in this report, I would like to introduce the documentation comments including JsDoc.

Report: Management of Images on Google Spreadsheet using Google Apps Script

This is a report for management of images on Google Spreadsheet using Google Apps Script.

At October 30, 2018, Cass OverGridImage and the method of inserImage have been added to Spreadsheet Service. At January 19, 2022, Class CellImageBuilder and Class CellImage have been added to Spreadsheet Service. By these Classes and methods, the images got to be able to be managed on Google Spreadsheet. But, when the image is used to the various situations, there are the cases that it is required to ingenuity to manage the images. So, in this report, I would like to introduce the management of images on Google Spreadsheet using the sample scripts of Google Apps Script.

Report: Challenging Exporting Selected Cells on Spreadsheet as Image using Google Apps Script and Javascript

This is a report for challenging exporting the selected cells on Spreadsheet as an image using Google Apps Script and Javascript.

Report: Efficiently Creating Web Apps using a Google Apps Script library

This is a sample script for efficiently creating Web Apps using a Google Apps Script library.

When a Google Apps Script library is used for creating Web Apps, the following advantage can be obtained.

  • The script of the client-side can be simpler. Because most scripts for constructing Web Apps are included in the Google Apps Script library.
  • When the script of Web Apps (In this case, the script of Google Apps Script library is modified.) is modified, the latest script is reflected in the Web Apps, immediately. Because when the Google Apps Script library is used as the latest version when the script of the library is modified, the client can use the latest script of the library, immediately. So, the downtime of Web Apps can be reduced.
    • By this, it is not required to manually reflect the latest version of the script to the Web Apps.
  • When you can change the script of Web Apps by changing the deployed version of the library.

Report: Process Cost of Google Apps Script During Large Calculations by Formulas on Google Spreadsheet

Report: Rule of Item IDs for Questions of Google Forms

Report: Implementing 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.

Best Practices for Discontinuous Cells on Google Spreadsheet by Google Apps Script

It has already been known that Google Apps Script is a strong tool for managing Google Spreadsheets. When the values are retrieved and/or put for Google Spreadsheet, there is a case that the discontinuous cells are required to be used. This report suggests the Best Practices for processing the discontinuous cells on Google Spreadsheet. From the results of process costs, it could understand the usefulness of using the discontinuous cells with low cost using Sheets API and Class RangeList of Spreadsheet service with Google Apps Script.

Report: Specification of Properties Service for Google Apps Script

In this report, the detailed specification of PropertiesService has been investigated. It is considered that knowing this specification will be useful for developing applications with Google Apps Script. As a result, it was found that the maximum key and value sizes are 524,287 bytes with a 1-byte key and 8,066 bytes, respectively. And also, it was found that the maximum size of PropertiesService is required to be considered with both the key and value sizes.

Report: Easily Implementing HTML Form with Google Spreadsheet as Database using Google Apps Script

This report introduces the method for easily implementing HTML forms with a Google Spreadsheet as a database using Google Apps Script. There are 2 patterns for the HTML form using Google Apps Script. One is that an HTML form is put into the same Google Apps Script project. Another is that an HTML form is put to a different server from a Google Apps Script project. In this report, the methods for easily implementing both patterns are introduced using the sample scripts.

Trend of google-apps-script Tag on Stackoverflow in first half of 2023

Recently, I felt a change like never before in the questions on Stackoverflow. In order to confirm this, in this report, the trend of "google-apps-script" tag on Stackoverflow in the first half (January 1st to June 1st) of 2023 has been investigated. From this report, in 2023 when the affection of COVID-19 has been reduced socially, the appreciable trend was confirmed to the questions including a tag of "google-apps-script". It is guessed that the origin of this appreciable trend is due to AI chatbots. The appearance of AI chatbots might give us a phase of major change to the method for understanding the statistical data obtained from online sites.

Understanding Flow of Request to Web Apps Created by Google Apps Script

Here, I would like to introduce a report for understanding the flow of the request to Web Apps created by Google Apps Script. There might be a case that various applications using the Web Apps are created and the Web Apps are used as the webhook. In that case, it is considered that when you have understood the flow of requests to the Web Apps, your goal might be able to be smoothly achieved. In this report, I would like to introduce the information about it.

Report: Values to transfer between Javascript and Google Apps Script with google.script.run

This report introduces the values that can be used for transferring between HTML and Google Apps Script with google.script.run.

Taking Advantage of Auto-completion of Script Editor for Google Apps Script

This is a report for taking advantage of the auto-completion of the script editor for Google Apps Script.

Report: How to Run Google Apps Script

Google Apps Script is one of the strong tools with cloud computing, and it is very useful for various situations. Google Apps Script can be run by various methods. This report introduces how to execute Google Apps Script. Recently, generative AI has given a lot of new users a chance to use Google Apps Script. If this report helps the users develop applications using Google Apps Script, I'm glad.

Dining Reservation System using Google Apps Script

This is the dining reservation system using Google Apps Script.


Benchmarks

Event Objects for Google Apps Script

  • It was found that the process cost of e.range.getA1Notation() was 20 % and 10 % for those of e.source.getActiveCell().getA1Notation() and SpreadsheetApp.getActiveSheet().getActiveCell().getA1Notation(), respectively.

Loop for Array Processing using Google Apps Script without V8

  • In the case of the sample script for retrieving the multiple of 5 from the array, the loop using "map, filter" is the most suitable way.
  • Ascending order of cost for each method is "map, filter", "Comprehension", "forEach", "for in", "for loop" and "while".
  • Cost for "forEach", "Comprehension" and "map, filter" is lower than that for "for in", "for loop" and "while".
  • Cost of push() and new Array() is almost the same.
  • When the array is changed from 1 dimensional array to 2 dimensional array, the increasing ratio of the cost for "Comprehension", "forEach" and "map, filter" is much lower than that for "for in", "for loop" and "while".
  • For the conventional method using "for loop", a new method could be proposed using the result of this report.
  • For "reduce", the process costs between 1 and 2 dimensional array are almost the same.

fetchAll method in UrlFetch service for Google Apps Script

  • It was found that the fetchAll method is worked by the asynchronous processing.
  • After it worked by the asynchronous processing, the returned values is reordered by the order of requests.
  • It was also found that if you want to retrieve the data from the several URL, the process cost of UrlFetchApp.fetchAll() is much lower than that of UrlFetchApp.fetch() using for loop.

Search for Array Processing using Google Apps Script

  • Process cost of search by indexOf() was the lowest of all methods.
  • 2nd and last one were the search by for loop and the search by the hash, respectively.
  • About the search by hash, although the cost of search by the hash from the object is very low, the cost for creating the object to search the hash was the highest of all. By this, the search by hash became the lowest rank. If the object for searching has already been created, the cost of search by the hash will be the lowest of all.
  • Search using indexOf can reduce the process cost of more than 99 % from the linear search and the search using hash.
  • From these results, it is considered that the scan for indexOf() may be different from the general for loop.

Conditional Branch using Google Apps Script

  • It was found that the cost of "Ternary operator" was the lowest of all methods and conditions.
  • For the single conditional branch, 2nd one was "If". But for the multiple conditional branches, "Switch" was the 2nd one. This indicates that "If" and "Switch" are suitable for the single and multiple conditional branches (more than 2 branches), respectively.
  • In the case of the multiple conditional branches, the process cost can be reduced by put the condition with the high possibility of "true" to the fore.
  • "Logical operator" was the lowest rank for the single and multiple conditional branches. It is considered that "Logical operator" is not suitable for the general use, because of the high cost and low readability.

Decreasing Loop for Array Processing using Google Apps Script

  • "filter using reversed array" makes the process cost be 43 % lower compare with "Decreasing for loop".
    • "reversed array" was obtained using "Array.prototype.reverse()" for the created a sample array.
  • "Decreasing for loop" is almost the same with "Increasing for loop".
  • Cost of "reverse()" is sufficiently small for "for loop" and "filter".

Reading and Writing Spreadsheet using Google Apps Script

In this report, the process cost for reading and writing Spreadsheet has been investigated. From this investigation, the following results were obtained.

  • For the process costs for reading values from Spreadsheet
    1. Process costs of getValues() and getSheetValues() of Spreadsheet Service are almost the same.
    2. Process costs of values.get and values.batchGet of Sheets API are almost the same.
    3. Methods of Sheets API can reduce the process costs from those of Spreadsheet Service by about 35 %.
  • For the process costs for writing values from Spreadsheet
    1. Process costs of values.update, values.batchUpdate and values.append of Sheets API are almost the same.
    2. Methods of Sheets API can reduce the process costs from those of Spreadsheet Service by about 19 %.
    3. There is the inversion point between setValues() of Spreadsheet Service and the methods of Sheets API.
      • When the data size is small, setValues() is suitable for writing values.
      • When the data size becomes large, the methods of Sheets API are suitable for writing values.

From these results, it is considered that the methods for reading and writing Spreadsheet of Sheets API are using the different algorithm and/or process from those of Spreadsheet service.

Importing CSV Data to Spreadsheet using Google Apps Script

In this report, the process cost for importing CSV data to Spreadsheet using GAS has been investigated. As the result, the following results were obtained.

  • It was found that pattern4, which uses pasteData of Sheets API, was the lowest cost of all.
  • When pattern4 is used for importing CSV data to Spreadsheet, the cost can be reduced by 56 % from pattern1 and pattern2, which use the method parsing and putting values.
  • When pattern4 is used for importing CSV data to Spreadsheet, the cost can be reduced by 72 % from pattern3, which use the method converting mimeType from CSV to Spreadsheet.

Benchmark: Loop for Array Processing using Google Apps Script with V8

In this report, the process cost of "loop" for the array processing using GAS with using V8 runtime has been investigated. As the result, it was found the following important features for GAS with V8.

  • In the case of the sample script for retrieving the multiple of 5 from the array, the loop costs using "for loop", "while", "forEach", "map, filter" and "reduce" are almost the same.
  • In the case of "for in", the process cost is higher than those of "for loop", "while", "forEach", "map, filter" and "reduce". But when that is compared with the condition without V8, the cost of "for in" with V8 is much lower than those without V8.
  • Costs of push() and new Array() are almost the same.
  • When v8 runtime is used for the loop process, the process cost could be largely reduced when it is compared with the script without V8.
    • For all methods of "for loop", "for in", "while", "forEach", "map, filter" and "reduce", the process costs of 97.0 % for 1D array and 98.4 % for 2D array could be reduced.

Benchmark: Process Costs under V8 using Google Apps Script

In this report, the process costs of 7 situations under V8 were measured. As the result, the following results could be obtained.

  1. Process cost with and without the arrow function was almost the same.
  2. Process cost "includes" and "indexOf" was almost the same.
  3. When the destructuring assignment is used, the cost was about 15 % higher than that without the destructuring assignment.
  4. Process cost with and without Map object was almost the same.
    • But in this case, the cost of Object.fromEntries is added for retrieving the result as the object. And when Object.fromEntries is not used, the cost with Map object was about 20 % lower than that without Map object.
  5. Process cost of Array.prototype.push was the lowest of Array.prototype.push.apply, Spread syntax and concat. The costs of Spread syntax and concat were about 3,040 % and 36,666 % higher than that of Array.prototype.push, respectively.
  6. Process cost of only reduce was the lowest of only reduce, Object.assign and Spread syntax. The costs of Object.assign and Spread syntax were about 265 % and 448,063 % higher than that of only reduce, respectively.
  7. Process costs of Array.from and only map were almost the same. The cost of Object.entries was about 131 % higher than that of Array.from.

Benchmark: Process Costs for Searching Values in Spreadsheet using Google Apps Script

Here, I would like to report the process costs for searching values in Spreadsheet using Google Apps Script (GAS). When the values are searched in Google Spreadsheet, the following 3 patterns can be considered. Ref

  1. Retrieve all values using getValues, and the values are searched from the retrieved array.
  2. Use TextFinder.
  3. Use Query language.

In these cases, it has already been found that the lowest process cost is to use the Query language. And about finding values from an array, I have already been reported as "Benchmark: Search for Array Processing using Google Apps Script". But I had never summarized the process costs for TextFinder and find values from an array. So in this report, I would like to introduce this. As the result, the importance of TextFinder for retrieving the row numbers and the row values by searching a value could be understand.

Benchmark: Process Costs for Retrieving Values from Arrays for Spreadsheet using Google Apps Script

Here, I would like to report the process costs for retrieving the values from the arrays for Spreadsheet using Google Apps Script (GAS). When Spreadsheet is used with Google Apps Script, we have the following situations.

  1. Retrieve values from the multiple rows in a column.
  2. Retrieve values from the multiple columns in a row.

When the values are retrieved from above situations, it is required to retrieve the values from 1 dimensional array in the 2 dimensional array. In this report, the process costs for retrieving the values from the 2 dimensional array of above situations have been measured.

As the result, it was found that when the values are retrieved from the arrays with n rows in a column and n columns in a row, to use the destructuring assignment and to use the index are suitable, respectively.

Benchmark: Measuring Process Costs for Formulas in Cells on Google Spreadsheet using Google Apps Script

When Google Spreadsheet is used, there is the case that the built-in functions and the custom functions in the cells are used. For the functions of Google Apps Script, there is the method for measuring the process cost. Ref But for the built-in functions, it is required to create the script for it. In this report, the script for measuring a function put in a cell has been proposed, and the process cost of the built-in functions has been measured. The proposed script can measure the process cost for the built-in functions and custom functions on Google Spreadsheet. The script is created with using Google Apps Script. When the process cost can be known for the built-in functions and custom functions, it is considered that it will be useful for the developers using Google Spreadsheet.

Benchmark: Process Costs for Retrieving 1st Empty Cell and 1st Non Empty Cell of Specific Column in Google Spreadsheet using Google Apps Script

Here, I would like to report the process costs for retrieving the 1st empty cell or 1st non empty cell of the specific column of Google Spreadsheet using Google Apps Script (GAS). For this situations, the following 2 patterns can be considered.

  1. Retrieving 1st empty cell of specific column by searching from TOP of sheet
  1. Retrieving 1st NON empty cell of specific column by searching from BOTTOM of sheet

Actually, when I create the applications using GAS, there is the case that it's required to retrieve the 1st empty cell or 1st non empty cell of the specific column. But, in such case, I would like to make the process cost of this as low as possible comparing with other part of the application. From such my experience, I thought that when the process cost of this can be reduced, it will be also useful for other users. So in this report, I would like to introduce the process cost of this situation. And, the following results were obtained.

  • In order to retrieve the 1st empty cell of the specific column by searching from TOP of sheet, the process cost of method using getNextDataCell is the lowest of all methods.
  • In order to retrieve 1st NON empty cell of specific column by searching from BOTTOM of sheet, the process cost of method using TextFinder is the lowest of all methods.

Benchmark: Concurrent Writing to Google Spreadsheet using Form

Benchmark: Process cost for HTML Template using Google Apps Script

When we use HTML in the Google Apps Script project, in order to show the values from the Google Apps Script side, the HTML template is used. When I used the HTML template with a large value, I understood that the process cost can be reduced by devising a script. In this report, I would like to introduce the process cost of the HTML template using the benchmark.

As the result, it was clearly found that when an HTML table is created with Google Apps Script and shows the HTML table with the HTML template, the process cost becomes low. And also, it was found that in order to reduce the process cost for using the HTML template, it is required to prepare the HTML data with the Google Apps Script side.

Benchmark: Process cost for Parsing XML data using Google Apps Script

In order to retrieve the values from XML data, when XML data is parsed using Google Apps Script, there are several methods for parsing the data. Class XmlService, which is a built-in Class for managing XML data, might be the first way to come up with it. At Stackoverflow, it is posted questions that XML data is often parsed using Class XmlService. It is considered that Class XmlService is suitable for managing XML data.

But, when I have created applications using XML data with Google Apps Script before, I have felt that the process of Class XmlService might be high. So, in this report, the process cost for parsing XML data using Google Apps Script. As the result, it was found that after the V8 runtime had been released, the methods except for Class XmlService in order to parse XML data can be also used.

Benchmark: High-Efficiency Finding and Replacing Many Values in Google Spreadsheet with Low Process Cost using Google Apps Script

This is a sample script for high-efficiency finding and replacing many values in Google Spreadsheet with the low process cost using Google Apps Script.

When the various values are replaced in Google Spreadsheet using Google Apps Script, I'm worried about the process cost. So, in this report, I would like to introduce a sample script for high-efficiency achieving this.

As the result, using a sample situation, when the process cost of the sample script using Sheets API is compared with that of the sample script using Spreadsheet services (SpreadsheetApp), it was found that the above script using Sheets API could reduce the process cost by about 70 % from the script using Spreadsheet service.

Benchmark: Process Costs for Checking Value in Array using Google Apps Script

There is a maximum executing time for Google Apps Script (GAS). That is 6 minutes. And, in the case of the custom function and the simple trigger, it is 30 seconds. So users always have to pay attention to reducing the process cost of the scripts. Especially, it is very important to know the process costs for the array processing, because array processing is often used for spreadsheets and Google APIs. I have already reported about the array processing at "Benchmark: Loop for Array Processing using Google Apps Script with V8" and "Search for Array Processing using Google Apps Script". In this report, the process cost checking a value in a one-dimensional array using Google Apps Script has been investigated.

Benchmark: Process Costs for Searching Value using Object with Google Apps Script

When a value is searched from the 1-dimensional array and a 2-dimensional array, after V8 runtime could be used, I use JSON object, Set object, and Map Object. But, I had never measured the process cost of this situation. In this post, I would like to introduce the process cost for searching a value using a JSON object, Set object, and Map object converted from the 1-dimensional array and 2-dimensional array.


Communities

Communities for Google Apps Script

Consumer (personal) version of Google+ is closed on April 2, 2019. By this, Apps Script community of Google+ is also closed. This is one of important communities for discussing. So in this post, I would like to introduce the other communities related to Google Apps Script.


Sample Scripts

Files in Google Drive

Projects

Spreadsheets

Documents

Slides

Gmail

Calendar

Form

YouTube

Chart

Analytics

Slack

Virtual Currency

Stackoverflow

Netatmo

Figma

Microsoft

In this case, the APIs and resources of Microsoft are used with Google Apps Script.

Etc

Node.js

Golang

Python

Curl

Javascript

PHP

Generative AI

TOP

About

Here, CLI tools, libraries, Add-ons, Reports, Benchmarks and Sample Scripts for taking advantage of Google Apps Script which are publishing in my blog, Gists and GitHub are summarized.

Topics

lists google-spreadsheet google-apps-script developer-tools google-api google-appscripts

Resources

Readme

License

MIT license
Activity

Stars

363 stars

Watchers

34 watching

Forks

36 forks
Report repository

Releases

No releases published

Packages

No packages published