grimelda

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

Verify and Repair Disk Permissions via Terminal (Mac OS X)

Verify Permissions

diskutil verifyPermissions /

Repair Permissions

diskutil repairPermissions /


	/**
	* Unpivot a pivot table of any size.
	* All credit to https://gist.github.com/philippchistyakov/57390ec98dcaea4502fabc5a32242b3a
	*
	* @param {A1:D30} data The pivot table.
	* @param {1} fixColumns Number of columns, after which pivoted values begin. Default 1.
	* @param {1} fixRows Number of rows (1 or 2), after which pivoted values begin. Default 1.
	* @param {"city"} titlePivot The title of horizontal pivot values. Default "column".
	* @param {"distance"[,...]} titleValue The title of pivot table values. Default "value".

	/**
	* Unpivot a pivot table of any size.
	*
	* @param {A1:D30} data The pivot table.
	* @param {1} fixColumns Number of columns, after which pivoted values begin. Default 1.
	* @param {1} fixRows Number of rows (1 or 2), after which pivoted values begin. Default 1.
	* @param {"city"} titlePivot The title of horizontal pivot values. Default "column".
	* @param {"distance"[,...]} titleValue The title of pivot table values. Default "value".
	* @return The unpivoted table
	* @customfunction

	// Simple function to add a menu option to the spreadsheet "Export", for saving a PDF of the spreadsheet directly to Google Drive.
	// The exported file will be named: SheetName and saved in the same folder as the spreadsheet.
	// To change the filename, just set pdfName inside generatePdf() to something else.

	// Running this, sends the currently open sheet, as a PDF attachment
	function onOpen() {
	var submenu = [{name:"Save PDF", functionName:"generatePdf"}];
	SpreadsheetApp.getActiveSpreadsheet().addMenu('Export', submenu);
	}