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

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
2 stars 36 forks Branches Tags Activity
Star
Notifications You must be signed in to change notification settings

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

BranchesTags
 
 

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

293 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, Add-ons, 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. 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 the 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 the important information and are much useful for a lot of people. As one of tags, there is "google-apps-script". I sometimes discuss at the questions with that tag. When we see the discussions, we can notice that the discussions are changed and progressed by the time, because "Google Apps Script" which is the origin of the tag is updated. This report thinks this change as the trend of tag of "google-apps-script". This trend includes the number of questions, questioners, answerers and tags adding to the tag of "google-apps-script". The trend of 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 of "google-apps-script", the tags added to this main tag strongly affected to the trend of the tag of "google-apps-script". Also the possibility which can estimate the future trend by investigating the tags adding 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.

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.


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


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

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.

Resources

Readme

License

MIT license
Activity

Stars

2 stars

Watchers

1 watching

Forks

2 forks
Report repository

Releases

No releases published

Packages

No packages published