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

MIT LicenseMIT

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 and GitHub are summarized. If these are useful for you, I'm glad.


Index



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.

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

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.

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.

Python library

  • getfilelistpy : This is a python library to retrieve the file list with the folder tree from the specific folder of Google Drive.

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.

Trend of google-apps-script Tag on Stackoverflow

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.


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

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


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

Chart

Slack

Virtual Currency

Stackoverflow

Netatmo

Etc

Node.js

Golang

Python

Curl

TOP