Gists
Published: September 15, 2021
Updated: September 17, 2021
From the discussions, added data by changing the wait time of LockService for Web Apps. Kanshi Tanaike
Introduction When the users try to write to a Spreadsheet using a form, the developers have to consider the concurrent submission from the form. For example, when multiple users submit the data with the form simultaneously, all data are possibly not to be saved to the Spreadsheet.
Gists
Introduction 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.
Retrieving 1st empty cell of specific column by searching from TOP of sheet
Retrieving 1st NON empty cell of specific column by searching from BOTTOM of sheet
Gists
Description 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.
Gists
Introduction 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.
Retrieve values from the multiple rows in a column.
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.
Gists
Introduction 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
Retrieve all values using getValues, and the values are searched from the retrieved array. Use TextFinder. Use Query language. In these cases, it has already been found that the lowest process cost is to use the Query language.
Gists
March 22, 2020 Published. Kanshi Tanaike
Introduction V8 engine got to be able to be used at Google Apps Script. By this, I have reported about the process costs with and without using V8. Ref It is considered that knowing the process costs for various methods will be useful for creating the applications with Google Apps Script. Here, I would like to introduce the process costs of each situations under V8.
Gists
Kanshi Tanaike
Introduction Please be careful! This result can be only used for Google Apps Script.
There are a limit executing time for Google Apps Script (GAS). That is 6 minutes.1 So users always have to pay attention to reducing the process cost of the scripts. Especially, it is very important to know the process cost for the array processing, because the array processing is often used for spreadsheet and Google APIs.
Gists
Kanshi Tanaike
Introduction Please be careful! This result can be only used for Google Apps Script.
There are a limit executing time for Google Apps Script (GAS). That is 6 minutes for Consumer and Google Apps free edition, and 30 minutes for G Suite and Early Access. 1 So many users always have to pay attention to reducing the process cost of scripts. So it is very important to know the process cost of various situations.
Gists
Benchmark: Reading and Writing Spreadsheet using Google Apps Script October 18, 2018 Updated. In order to compare with Advanced Google Service, a result of Sheets API by UrlFetchApp was added to Appendix.
Kanshi Tanaike
Introduction Please be careful! This result can be only used for Google Apps Script.
There are a limit executing time for Google Apps Script (GAS). That is 6 minutes for Consumer and Google Apps free edition, and 30 minutes for G Suite and Early Access.
Gists
Benchmark: Decreasing Loop for Array Processing using Google Apps Script Kanshi Tanaike
Introduction Please be careful! This result can be only used for Google Apps Script.
There are a limit executing time for Google Apps Script (GAS). That is 6 minutes. 1 So users always have to pay attention to reducing the process cost of the scripts. Especially, it is very important to know the process cost for the array processing, because the array processing is often used for spreadsheet and Google APIs.
I updated the report of “Benchmark: Loop for Array Processing using Google Apps Script”. In this update, “reduce” was added. If this report is useful for your situation, I'm glad.
**You can check this at https://gist.
Gists
Benchmark: Conditional Branch using Google Apps Script Kanshi Tanaike
Introduction Please be careful! This result can be only used for Google Apps Script.
There are a limit executing time for Google Apps Script (GAS). That is 6 minutes.1 So users always have to pay attention to reducing the process cost of the scripts. Especially, it is very important to know the process cost for the array processing, because the array processing is often used for spreadsheet and Google APIs.
Gists
Benchmark: Search for Array Processing using Google Apps Script Kanshi Tanaike
Introduction Please be careful! This result can be only used for Google Apps Script.
There are a limit executing time for Google Apps Script (GAS). That is 6 minutes.1 So users always have to pay attention to reducing the process cost of the scripts. Especially, it is very important to know the process cost for the array processing, because the array processing is often used for spreadsheet and Google APIs.
Gists
By Google's update at January 19, 2018, fetchAll method was added to the UrlFetch service. When I saw the usage, I couldn't find the detail information about the actual running state. So I investigated about it.
As the result, it was found that the fetchAll method is worked by the asynchronous processing. The returned data is reordered by the order of requests. By this, it was also found that if you want to retrieve the data from the several URL, the process cost of UrlFetchApp.
Gists
Benchmark: Loop for Array Processing using Google Apps Script July 26, 2018 Updated. Result of reduce was added.
Kanshi Tanaike
Introduction Please be careful! This result can be only used for Google Apps Script.
There are a limit executing time for Google Apps Script (GAS). That is 6 minutes.1 So users always have to pay attention to reducing the process cost of the scripts. Especially, it is very important to know the process cost for the array processing, because the array processing is often used for spreadsheet and Google APIs.
Gists
Introduction There are event objects at Google Apps Script. Typically, users which use Spreadsheet often use onEdit(event). Here, I would like to introduce the process costs for the event objects using this onEdit(event).
When onEdit(event) is used for the spreadsheet, event of onEdit(event) has the following structure.
{
"authMode": {},
"range": {
"columnStart": 1,
"rowStart": 1,
"rowEnd": 1,
"columnEnd": 1
},
"source": {},
"oldValue": "old sample text",
"user": {
"nickname": "sampleName",
"email": "sample email"
},
"value": "sample text"
}
In this structure, for example, the range of active cell is "range": {"columnStart": 1, "rowStart": 1, "rowEnd": 1, "columnEnd": 1 }.
This sample script is for retrieving values from a deep nested JSON. There are 2 patterns. So for these, the benchmark were measured.
Script : package main import ( "encoding/json" "testing" ) const ( data = `{ "A_key1": { "B_key1": { "C_key": "value" } } }` ) func BenchmarkB1(b *testing.B) { b.ResetTimer() for i := 0; i < b.N; i++ { var p map[string]interface{} json.Unmarshal([]byte(data), &p) a1 := p["A_key1"] a2 := p["A_key1"].
Description There are a limit executing time for Google Apps Script (GAS). It's 6 minutes. So users have to pay attention to the process cost of the script. GAS can use JavaScript 1.7. This means to be able to be used comprehension for GAS.
In this report, the process cost for the comprehension has been investigated. The normal for loop was used as the competitor. As a result, it was found that the comprehension can be used one of methods for reducing the process cost.
This sample script is for splitting command-line arguments by golang. There are 2 types. One is the regular expression is used. Another is that Split() and TrimSpace() are used.
Here, each process speed was compared.
Script : package main import ( "regexp" "strings" "testing" ) func BenchmarkB1(b *testing.B) { str := "test1.txt, test2.txt" b.ResetTimer() for i := 0; i < b.N; i++ { ar := regexp.MustCompile(`\s*,\s*`).Split(str, -1) var result []string for _, x := range ar { result = append(result, x) // --> 'test.
This “souwapy” is a library for summing array elements with high speed by new algorithm (Pyramid method). The speed is faster than csv and panbdas module of python and v8 engine of node.js. The souwapy module is 2.3 and 3.1 times faster than csv and pandas module, respectively. This was really surprised me. It was found that the theory was correct.
At first, I have created this theory for Google Apps Script.
Abstract I have already reported that the pyramid method is one of very effectively algolithms for summing string elements in an array using Google Apps Script (GAS). This report describes the adaptability of the pyramid method to any languages except for GAS. c++ (g++), Go, Java, Javascript on Node.js, Python and Ruby were chosen as the sample languages. In those languages, there are languages which have the distinctive commands for summing the array elements.
Abstract 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.