All Projects β†’ tanaikech β†’ Taking Advantage Of Google Apps Script

tanaikech / Taking Advantage Of Google Apps Script

Licence: mit
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.

Projects that are alternatives of or similar to Taking Advantage Of Google Apps Script

Awesome Awesome Nodejs
πŸ’πŸš€ An Awesome list of Awesome lists related to Node.js.
Stars: ✭ 1,389 (+1029.27%)
Mutual labels:  lists, developer-tools
Dksnap
Docker Snapshots for Development and Test Data
Stars: ✭ 122 (-0.81%)
Mutual labels:  developer-tools
Cutbox
CutBox makes your macOS pasteboard awesome.
Stars: ✭ 112 (-8.94%)
Mutual labels:  developer-tools
Nanobox
The ideal platform for developers
Stars: ✭ 1,530 (+1143.9%)
Mutual labels:  developer-tools
Shapy
Shapy is a CSS gradient editor that lets you size, position and layer CSS gradients on a single div element.
Stars: ✭ 115 (-6.5%)
Mutual labels:  developer-tools
Jacoco Plugin
Jenkins JaCoCo Plugin
Stars: ✭ 119 (-3.25%)
Mutual labels:  developer-tools
Starters
R Package πŸ“¦ for initializing projects for various R activities πŸ”©
Stars: ✭ 111 (-9.76%)
Mutual labels:  developer-tools
Awesome Sweden
A curated list of awesome things to use when coding for the Swedish market.
Stars: ✭ 124 (+0.81%)
Mutual labels:  lists
Dep viz
A visual tool to help developers understand Elixir recompilation in their projects
Stars: ✭ 121 (-1.63%)
Mutual labels:  developer-tools
Eruda
Console for mobile browsers
Stars: ✭ 11,547 (+9287.8%)
Mutual labels:  developer-tools
Materialabout
It's a material-design about screen to use on your Android apps. A developer profile and application information easy to integrate. πŸ”–
Stars: ✭ 1,511 (+1128.46%)
Mutual labels:  developer-tools
Kubernaut
Instant ephemeral Kubernetes clusters for development and testing
Stars: ✭ 115 (-6.5%)
Mutual labels:  developer-tools
Carvel Kwt
Kubernetes Workstation Tools CLI
Stars: ✭ 119 (-3.25%)
Mutual labels:  developer-tools
Admiral.cr
A robust DSL for writing command line interfaces written in Crystal.
Stars: ✭ 112 (-8.94%)
Mutual labels:  developer-tools
Storybook Addon
Develop themable components with Emotion/Styled Components/Material-UI with help of Storybook & React Theming
Stars: ✭ 122 (-0.81%)
Mutual labels:  developer-tools
Analyzer Of Android For Apache Weex
Stars: ✭ 111 (-9.76%)
Mutual labels:  developer-tools
Admidio
Admidio is a free open source user management system for websites of organizations and groups. The system has a flexible role model so that it’s possible to reflect the structure and permissions of your organization.
Stars: ✭ 116 (-5.69%)
Mutual labels:  lists
Accesscontrolkitty
Xcode extension - quickly change access control level (public, private etc.) of selected Swift code by Editor menu item or keyboard command
Stars: ✭ 119 (-3.25%)
Mutual labels:  developer-tools
Service Worker Detector
This extension detects if a website registers a Service Worker.
Stars: ✭ 124 (+0.81%)
Mutual labels:  developer-tools
Awesome Dos
Curated list of references for development of DOS applications.
Stars: ✭ 123 (+0%)
Mutual labels:  lists

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



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.

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

  • GetFileList_js : This is a Javascript library to retrieve the file list with the folder tree from the specific folder (publicly shared folders and own folders) of Google Drive.
  • syncGoogleScriptRun : This is a Javascript library to use "google.script.run" with the synchronous process.
  • ResumableUploadForGoogleDrive_js : This is a Javascript library to achieve the resumable upload for Google Drive.
  • BatchRequest_js : This is a library for running Batch Requests for Google APIs using Javascript.
  • HtmlFormObjectParserForGoogleAppsScript_js : This is a Javascript library for sending the HTML form object to Google Apps Script using google.script.run.

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.


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.


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

Chart

Analytics

Slack

Virtual Currency

Stackoverflow

Netatmo

Figma

Etc

Node.js

Golang

Python

Curl

Javascript

TOP

Note that the project description data, including the texts, logos, images, and/or trademarks, for each open source project belongs to its rightful owner. If you wish to add or remove any projects, please contact us at [email protected].