TheGreatGaspy

Creating Multiple Buttons on Google Spreadsheet using Google Apps Script

This is a sample script for creating the multiple buttons on Google Spreadsheet using Google Apps Script.

Recently, I have got several contacts about this. I thought that when this is published, it might be useful for other users. So I published this sample script.

Sample script

TheGreatGaspy

Converting Values of Google Spreadsheet to Object using Google Apps Script

This is a sample script for converting the values of Google Spreadsheet to an object using Google Apps Script.

Sample script

function myFunction() {

TheGreatGaspy

Importing Microsoft Excel to Google Spreadsheet using Custom Function with Google Apps Script

This is a sample script for importing Microsoft Excel (XLSX) data to Google Spreadsheet using a custom function with Google Apps Script.

Usage

1. Install SheetJS library.

TheGreatGaspy

Transposing JSON Object using Google Apps Script

This is a sample script for transposing JSON object using Google Apps Script.

Input data :

[
    {"key1":"a1","key2":"a2","key3":"a3","key4":"a4","key5":"a5"},
    {"key1":"b1","key2":"b2","key3":"b3","key4":"b4","key5":"b5"},
    {"key1":"c1","key2":"c2","key3":"c3","key4":"c4","key5":"c5"},
 {"key1":"d1","key2":"d2","key3":"d3","key4":"d4","key5":"d5"},

TheGreatGaspy

Retrieving Names of Month and Day of Week using Google Apps Script

This is a sample script for retrieving the names of month and day of week using Google Apps Script.

I think that you might have a case that you want to retrieve the names of month and day of week using Google Apps Script. This sample script retrieves them using a simple script.

Using Utilities.formatDate of Google Apps Script, the names of month and day of week can be retrieved using a simple script.

Sample script 1

TheGreatGaspy

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

April 16, 2018 Published.

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

TheGreatGaspy

Letting Users Running Google Apps Script on Google Spreadsheet without both Authorizing Scopes and Showing Script

This is a sample workaround for letting users running Google Apps Script on Google Spreadsheet without both authorizing the scopes and showing the script.

The flow of this workaround is as follows.

1. Create Web Apps created by Google Apps Script and deploy it as Web Apps. As the returned value, the XML data is returned.
   • Your script can be included in this script.
2. User put a formula of =IMPORTML("WebApps URL", "xpath") to a cell.

TheGreatGaspy

Retrieving Named Functions from Google Spreadsheet using Google Apps Script

This is a sample script for retrieving the named functions from Google Spreadsheet using Google Apps Script.

Recently, the named functions got to be able to be used in Google Spreadsheet. Ref When several named functions are added, I thought that I wanted to retrieve these functions using a script. But, unfortunately, in the current stage, it seems that there are no built-in methods (SpreadsheetApp and Sheets API) for directly retrieving the named functions. So, I created this sample script.

In this script, the following flow is run.

TheGreatGaspy

Copying Google Spreadsheet by Removing Container-Bound Script Using Google Apps Script

This is a sample script for copying a Google Spreadsheet including a container-bound script by removing the container-bound script using Google Apps Script.

When you want to copy a Google Google Spreadsheet including a container-bound script by removing the container-bound script using Google Apps Script, this could be achieved by using "get" and "create" methods of Sheets API before. The sample script is as follows.

const obj = Sheets.Spreadsheets.get(

TheGreatGaspy

Retrieving Event ID from Event URL of Google Calendar using Google Apps Script

This is a sample script for retrieving the event ID from the event URL of Google Calendar using Google Apps Script.

The event URL is like https://www.google.com/calendar/event?###. At the event URL, ### of https://www.google.com/calendar/event?### is not the event ID. So it is required to convert it.

Sample script

var eventUrl = "https://www.google.com/calendar/event?###";