Alex Ivanov oshliaer

Straightening Elements in 2 Dimensional Array using Google Apps Script

This sample script is for straightening elements in 2 dimensional array using Google Apps Script (GAS). When applications using Spreadsheet are developed by GAS, it usually uses 2 dimensional array by setValues(). And the lengths of each element are required to be the same. On the other hand, data used for the applications might not be the same length for each element in 2 dimensional array. This sample script can be used under such situation.

Sample 1 :

var data = [
  ["string"],
  ["string", "string", "string", "string", "string"],
  ["string", "string", "string"],
 ["string", "string", "string", "string", "string"],

Learning Google Apps Script

The best resource for learning Google Script is the official documentation available at developers.google.com. Here are other places that will help you get up to speed.

Case Studies - Sample Google Script Projects (google.com)
Google Apps Scripts - Snippets by +Amit Agarwal
scoop.it/t/gas by +Martin Hawksey
Awesome Google Scripts by +Amit Agarwal
O'Reilly - Apps Script - by +James Ferreira
Apps Script Webinars - YouTube - by +Eric Koleda and +Arun Nagarajan

Enhanced onEdit(e) using Google Apps Script

onEdit(e) which is used for the Edit event on Spreadsheet has the old value as e.oldValue. The specifications for this are as follows.

When an user edited a single "A1" cell, e of onEdit(e) shows hoge for e.oldValue and fuga for e.value.
When an user edited the "A1:A2" multiple cells, e.oldValue and e.value of onEdit(e) are not shown anything.
When an user copied and pasted from other cell, e.oldValue and e.value of onEdit(e) are not shown anything.

This sample script was created to retrieve both the edited values and the old values for the range of edited cells. This is the modified e.oldValue.

Exporting Project on Google Drive using Golang Quickstart

This is a sample script for exporting a project on Google Drive to local PC using Golang Quickstart. A file with refresh token is saved to the same directory with this script as go-quickstart.json. Before you run this script, please enable Drive API on your Google API console.

Points for exporting project

In order to export project, both drive.DriveScriptsScope and drive.DriveScope have to be included in the scope.
The mimeType for exporting has to be "application/vnd.google-apps.script+json".

If you already have the file with refresh token, at first, please delete it and run this script. By this, the scopes of refresh token and access token are updated.

Script :

Downloading Shared Files on Google Drive Using Curl

When the shared files on Google Drive is downloaded, it is necessary to change the download method by the file size. The boundary of file size when the method is changed is about 40MB.

File size < 40MB

CURL

filename="### filename ###"
fileid="### file ID ###"
curl -L -o ${filename} "https://drive.google.com/uc?export=download&amp;id=${fileid}"

Retrieving Access Token From OneDrive using Google Apps Script

Overview

This GAS sample is for retrieving access token to use OneDrive APIs using Google Apps Script.

In this script, the authorization code is automatically retrieved.

	/**
	*
	* Campaign Budget Overspend Monitoring
	*
	* This script labels campaigns whose spend today is more than their daily
	* budgets. Optionally, it also pauses campaigns whose spend exceeds the
	* budget by too much. An email is then sent, listing the newly labelled
	* and paused campaigns.
	* When spend no longer exceeds budget, the campaigns are reactivated and
	* labels are removed.

	<!-- image -->
	<figure {{ with .Get "class" }}class="{{.}}"{{ end }}>
	{{ with .Get "link"}}<a href="{{.}}">{{ end }}
	<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-src="{{ .Get "src" }}" {{ if or (.Get "alt") (.Get "caption") }}alt="{{ with .Get "alt"}}{{.}}{{else}}{{ .Get "caption" }}{{ end }}" {{ end }}{{ with .Get "width" }}width="{{.}}" {{ end }} class="lazyload" />
	<noscript>
	<img src="{{ .Get "src" }}" {{ if or (.Get "alt") (.Get "caption") }}alt="{{ with .Get "alt"}}{{.}}{{else}}{{ .Get "caption" }}{{ end }}" {{ end }}{{ with .Get "width" }}width="{{.}}" {{ end }}/>
	</noscript>
	{{ if .Get "link"}}</a>{{ end }}
	{{ if or (or (.Get "title") (.Get "caption")) (.Get "attr")}}
	<figcaption>{{ if isset .Params "title" }}

	/*
	* script to export data of the named sheet as an individual csv files
	* sheet downloaded to Google Drive and then downloaded as a CSV file
	* file named according to the name of the sheet
	* original author: Michael Derazon (https://gist.github.com/mderazon/9655893)
	*/

	function onOpen() {
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var csvMenuEntries = [{name: "Download Primary Time File", functionName: "saveAsCSV"}];

	// IfcGuid
	// ----------------------------------------------------------------------------
	// This class is a service class providing methods to generation and conversion
	// between compressed and uncompressed string representations of GUIDs
	// according to the algorithms used by the Industry Foundation Classes (IFC).
	// The algorithm is based on an implementation in c as follows:
	// originally proposed by Jim Forester.
	// implemented previously by Jeremy Tammik using hex-encoding
	// Peter Muigg, June 1998
	// Janos Maros, July 2000