tanaikech

Measuring Execution Time of Built-In Functions for Google Spreadsheet

This sample script is for measuring the execution time of built-in functions for Google Spreadsheet. Unfortunately, there are not measurement tools for retrieving the execution time of built-in functions. So I thought of about a workaround.

Flow :

1. Import a value to a cell. The value is anything good, because this is used as a trigger. Please do this by yourself.
   • Custom functions cannot use setValue(). So I used onEdit().
2. func1() imports a formula that you want to measure the execution time by the script launched by the trigger.
3. At func2(), after set the formula, the measurement is started. The confirmation when built-in function was completed is carried out using loop.
   • By measuring the cost per one call for getValue(), it was found that that was about 0.0003 s. So I thought that this can be used.
4. The result of measurement can be seen at Stack

tanaikech

Converting a1Notation to GridRange for Google Sheets API

When it uses Google Sheets API v4, GridRange is used for it as the range property. These sample scripts are for converting from a1Notation to GridRange. You can chose from following 2 scripts. Both scripts can retrieve the same result.

Script 1 :

This is from me.

function a1notation2gridrange1(sheetid, a1notation) {
  var data = a1notation.match(/(^.+)!(.+):(.+$)/);
 var ss = SpreadsheetApp.openById(sheetid).getSheetByName(data[1]);

Max-Makhrov

// usage
function cars_importRef() {
  var t = new Date();
  // YOUR CODE
  Browser.msgBox('Done! The time to run function is ' + getTimeEllapse_(t));
}

function getTimeEllapse_(t)
{
  var dif = new Date() - t;

tanaikech

Retrieving Difference Between 2 Arrays using Google Apps Script

This is a sample script for retrieving the difference between 2 arrays, which are the old values and the new values, using Google Apps Script. In my environment, I sometimes have the situation that it is required to retrieve the difference between 2 arrays. So I prepared this as a sample script. I think that this can be also used at Javascript and Node.js. If this was also useful for your situation, I'm glad.

Sample script

In this sample script, the difference of the values of oldValues and newValues is retrieved as an object.

const getDiffFrom2Arrays = (oldValues, newValues) => {

tanaikech

Statistics of appscript Tag on Stackoverflow

This is the statistics for the tag appscript on Stackoverflow. This statistics are retrieve from Stackoverflow using Stackexchange API. About 2020, the data is retrieved from 2020-01-01 to 2020-07-01.

Recently, this tag is modified by editing the tags. So I think that it has to be said that this statistics data is obtained at 2020-07-06.

Measurement result

tanaikech

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

- February 9, 2020 - Published. - March 15, 2020 - Results of "for of" and "for of with iterator" were added.

tanaikech

Switching Buttons for Google Spreadsheet using Google Apps Script

These are the sample scripts for achieving the switching buttons for Google Spreadsheet using Google Apps Script. The management of images using Spreadsheet service is growing now. But, in the current stage, in order to achieve the switching buttons, it needs a little ingenuity. In this report, I would like to introduce 4 kinds of the switching buttons.

Pattern 1

In this pattern, the drawing is used as the button.

tanaikech

Change Tab Detection on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script

onSelectionChange has been released at April 22, 2020. But this couldn't be used at the released day. But now, I could confirm that this got to be able to be used. So in order to test this event trigger, I prepared a simple sample script. This is a sample script for detecting the change tab on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script.

Demo

Usage

tanaikech

Modifying 1st-Page Header in Google Document using Google Apps Script

These are sample scripts for modifying the 1st-page header in Google Document using Google Apps Script. Unfortunately, in the current stage, the 1st-page header cannot be modified by Document service. In this case, it is required to use Google Docs API. Here, I would like to introduce 2 sample scripts for modifying the 1st page header using Docs API.

When you use this, please enable Google Docs API at Advanced Google services.

Flow

The flow of this is as follows.

tanaikech

Replacing Multiple Values in Google Spreadsheet with Low Process Cost using Google Apps Script

This is a sample script for replacing the multiple values with various values in Google Spreadsheet with the low process cost using Google Apps Script. In this script, the batchUpdate method of Sheets API is used. So the request can be done by one API call. When Spreadsheet services is used for this situation, the values are retrieved from the sheet and replaced the retrieved values, and then, the updated values are put to the sheet. Or the TextFinder is used in a loop. In this case, the process cost is higher than that using Sheets API. By using the bathUpdate method of Sheets API, the process cost is reduced.

Sample script

Before you use this script, please enable Sheets API at Advanced Google services. [Ref](https://developers.google.com/apps-script/guides/services/advanced#enabling_a