Alex Ivanov oshliaer

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

April 16, 2018 Published.

July 26, 2018 Updated. Result of reduce was added.

Benchmark: Event Objects for Google Apps Script

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": {},

Append Values by Inserting Rows using Google Sheets API

In the case appending values to cell by inserting rows, when sheets.spreadsheets.values.append is used, the values are appended to the next empty row of the last row. If you want to append values to between cells with values by inserting row, you can achieve it using sheets.spreadsheets.batchUpdate.

When you use this, please use your access token.

Endpoint :

POST https://sheets.googleapis.com/v4/spreadsheets/### spreadsheet ID ###:batchUpdate

Препроцессор Pug(Jade)

Pug - это препроцессор HTML и шаблонизатор, который был написан на JavaScript для Node.js.

Batching Requests for Google Apps Script

There is the bathing requests in the Google APIs. The bathing requests can use the several API calls as a single HTTP request. By using this, for example, users can modify filenames of a lot of files on Google Drive. But there are limitations for the number of API calls which can process in one batch request. For example, Drive API can be used the maximum of 100 calls in one batch request.

The following sample script modifies the filenames of 2 files on own Google Drive using Google Apps Script. Drive API for modifying filenames is used as "PATCH". The batch request including 2 API calls of Drive API is called using "multipart/mixed". If you want more APIs, please add the elements to the array of "body".

function main() {
  var body = [
    {
 method: "PATCH",

Automatic Recalculation of Custom Function on Spreadsheet Part 1

In this report, I would like to introduce a workaround for automatically recalculating custom functions on Spreadsheet.

1. Situation

The sample situation is below. This is a sample situation for this document.

There are 3 sheets with "sheet1", "sheet2" and "sheet3" of sheet name in a Spreadsheet.
Calculate the summation of values of "A1" of each sheet using a custom function.
Sample script of the custom function is as follows.

	/* The following code produces the following output in stackdriver log (newer logs higher):
	Time for concurrent: 5.848 seconds.
	Time for sleeping 10: 5.003 seconds.
	Time for sleeping 2: 5.002 seconds.
	Time for sleeping 9: 5.004 seconds.
	Time for sleeping 8: 5.002 seconds.
	Time for sleeping 5: 5.003 seconds.
	Time for sleeping 3: 5.003 seconds.
	Time for sleeping 1: 5.003 seconds.
	Time for sleeping 7: 5.003 seconds.

	(function(global,name,Package,helpers){var ref=function wrapper(args){var wrapped=function(){return Package.apply(global.Import&&Import.module?Import._import(name):global[name],[global].concat(Array.prototype.slice.call(arguments)))};for(i in args){wrapped[i]=args[i]}return wrapped}(helpers);if(global.Import&&Import.module){Import.register(name,ref)}else{Object.defineProperty(global,name,{value:ref});global.Import=global.Import\|\|function(lib){return global[lib]};Import.module=false}})(this,

	"Requests",

	function RequestsPackage_ (global, config) {
	var self = this, discovery, discoverUrl;

	discovery = function (name, version) {
	return self().get('https://www.googleapis.com/discovery/v1/apis/' + name + '/' + version + '/rest');
	};

	/**
	* Pretends to take a long time to return two rows of data
	*
	* @param {string} endpoint
	* @return {ResponseObject}
	*/
	function doSomething (endpoint) {
	Utilities.sleep(5 * 1000); // sleep for 5 seconds
	return {
	numberOfRows: 2,

	function numberWithSpaces(nr) {
	return nr.toString().replace(/\B(?=(\d{3})+(?!\d))/g, " ");
	}