Notes - Google Apps Script #notes

00-Google Apps Script.md

Google Apps Script

⏫ 🔽

Basic

• https://developers.google.com/apps-script/overview
• https://developers.google.com/apps-script/reference
• https://developers.google.com/apps-script/api
• https://script.googleapis.com/$discovery/rest
• https://developers.google.com/apis-explorer

Reference

• OAuth2 library

G Suite

• G Suite API access with domain-wide delegation
• Google Issue Tracker Apps Scripts

Clasp

with Vue

• https://github.com/clomie/gas-vue-typescript
• https://github.com/ecnopdev/vuejs-gas-boilerplate (not Clasp)

run local

• https://github.com/iamsajidjaved/Single-Page-App-Google-Apps-Script-

Template

• https://github.com/dustinmichels/clasp-template

Collection

• Google Apps Script Library Database
• Google Apps Script Awesome List
• https://sites.google.com/site/scriptsexamples/custom-methods

Drive

• https://github.com/tanaikech/ZipFolder
• https://github.com/tanaikech/FilesApp
• https://github.com/tanaikech/Batch-Requests-for-Drive-API-using-Google-Apps-Script#transferring-owner-of-files
• Managing Shared Drive using Drive Service
• DriveCopy
• Watch Google Drive and send Email

Form

• https://forms.studio/
• https://support.form-publisher.com/hc/en-us

Firebase

• Cloud Function for Firebase

Web App

• https://github.com/tanaikech/taking-advantage-of-Web-Apps-with-google-apps-script

Framework

• https://sheetbase.dev/
• https://github.com/sheetbase

Snippets

• https://github.com/dustinmichels/GAS-snippets
• https://sites.google.com/site/scriptsexamples/custom-methods

External Integration

• OneDrive

Resources

• https://stackoverflow.com/questions/tagged/google-apps-script
• https://groups.google.com/g/google-apps-script-community
• https://pulse.appsscript.info/
• GAS Slack
• https://yagisanatode.com/google-apps-script/

People

• Apps Script consultants
• https://andyconlin.com/freelancer-directory/

⏫

---

Codes

01-Basic Apps Script snippets.md

Collection of basic Apps Script snippets to get started.

annotation.md

Annotation

https://developers.google.com/apps-script/guides/services/authorization#manual_authorization_scopes_for_sheets_docs_slides_and_forms

/**
* @OnlyCurrentDoc
*/

date.js

// Date related snippets

externalLib.js

// Load JavaScript from External Server
function loadJSFromServer() {
    var url = "https://example.com/script.text";
    var javascript = UrlFetchApp.fetch(url).getContentText();
    eval(javascript);
}

// Load JavaScript from Google Drive
function loadJSFromGoogleDrive() {
    var rawJS = DriveApp.getFileById(id).getBlob().getDataAsString();
    eval(rawJS);
}


// Load a few libraries
var LIBRARIES = {
    prettyDate:  "http://ejohn.org/files/pretty.js",
    underScore: "http://underscorejs.org/underscore-min.js",
}
  
Object.keys(LIBRARIES).forEach(function(library) {
    newFunc = loadJSFromUrl(LIBRARIES[library]);
    eval('var ' + library + ' = ' + newFunc);
});
  
function loadJSFromUrl(url) {
    return eval(UrlFetchApp.fetch(url).getContentText());
}

fetchJsonObject.js

/** 
 * Helper function that retrieves a JSON object containing a
 * response from a public API.
 *
 * @param {string} url The URL of the API object being fetched. 
 * @return {object} resourceObject The JSON object fetched 
 *   from the URL request to the API. 
 */
function fetchApiResourceObject_(url) {
  // Make request to API and get response.
  var response =
    UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  
  // Parse and return the response as a JSON object.
  var json = response.getContentText();
  var responseObject = JSON.parse(json); 
  return responseObject; 
}

formatDates.js

/** 
 * Helper method that applies a
 * "Month Day, Year (Day of Week)" date format to the
 * indicated column in the active sheet. 
 *
 * @param {number} colIndex The column index of the column
 *   to format.
 */ 
function formatDates_(colIndex) {
  // Exit if the given column index is -1, indicating
  // the column to format isn't present in the sheet.
  if (colIndex < 0)
    return; 

  // Set the date formatting for the date column, excluding
  // the header row.
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2, colIndex, sheet.getLastRow() - 1, 1)
    .setNumberFormat("mmmm dd, yyyy (dddd)");
}

formatRowHeader.js

/**
 * Formats top row of the sheet using our header row style. 
 */
function formatRowHeader() {
    // Get the current active sheet and the top row's range.
    var sheet = SpreadsheetApp.getActiveSheet();
    var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
   
    // Apply each format to the top row: bold white text,
    // blue-green background, and a solid black border
    // around the cells.
    headerRange
      .setFontWeight('bold')
      .setFontColor('#ffffff')
      .setBackgroundColor('#007272')
      .setBorder(
        true, true, true, true, null, null,
        null,
        SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  
  }

hyperlinkRow.js

/**
 * Helper function that hyperlinks the column header with the
 * 'url' contents. The function then removes the url column.
 *
 * @param {object} headerRange The range of the column header
 *   to update.
 * @param {number} numRows The size of the column header.
 */
function hyperlinkColumnHeaders_(headerRange, numRows) {
  // Get header and url column indices.
  var headerColIndex = 1; 
  var urlColIndex = columnIndexOf_('url');  
  
  // Exit if the url column is missing. 
  if(urlColIndex == -1)
    return; 
  
  // Get header and url cell values.
  var urlRange =
    headerRange.offset(0, urlColIndex - headerColIndex);
  var headerValues = headerRange.getValues();
  var urlValues = urlRange.getValues();
  
  // Updates header values to the hyperlinked header values. 
  for(var row = 0; row < numRows; row++){
    headerValues[row][0] = '=HYPERLINK("' + urlValues[row]
      + '","' + headerValues[row] + '")'; 
  }
  headerRange.setValues(headerValues); 
  
  // Delete the url column to clean up the sheet.
  SpreadsheetApp.getActiveSheet().deleteColumn(urlColIndex); 
}

/**
 * Helper function that goes through the header of all the columns 
 * and returns the index of the column with the specified name
 * in row 1. If a column with that name does not exist,
 * this function returns -1. If multiple columns have the same
 * name in row 1, the index of the first one discovered is
 * returned.
 * 
 * @param {string} colName The name to look for in the column
 *   headers. 
 * @return The index of that column in the active sheet,
 *   or -1 if the name isn't found.
 */ 
function columnIndexOf_(colName) {
  // Get the current column names.
  var sheet = SpreadsheetApp.getActiveSheet();
  var columnHeaders =
    sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var columnNames = columnHeaders.getValues();
  
  // Loops through every column and returns the column index
  // if the row 1 value of that column matches colName.
  for(var col = 1; col <= columnNames[0].length; col++)
  {
    if(columnNames[0][col-1] === colName)
      return col; 
  }

  // Returns -1 if a column named colName does not exist. 
  return -1; 
}

lock.js

// https://developers.google.com/apps-script/reference/lock/lock

  var lock = LockService.getScriptLock()  
  lock.tryLock(10000)  
  try {  

  }  
  catch (e) {  
 
  }  
  finally {  
   lock.releaseLock()  
  }  

//

var lock = LockService.getScriptLock();
try {
  lock.waitLock(10000);
} catch (e) {
  Logger.log('Could not obtain lock after 10 seconds.');
}

logging.js

function hello() {
  Logger.log("Hello, " + world);
  Logger.log(JSON.stringify(e,null,2));
}

messages.js

// If there aren't any charts, display a toast
// message - a small 'peek' dialog that pops up in the lower-right corner of Sheets, 
//stays for a few seconds, and then disappears
// 
var ss = SpreadsheetApp.getActiveSpreadsheet();
if (charts.length == 0) {
  ss.toast('No charts to export!');
  return;
  

// Create and display a dialog that tells the user where to
// find the new presentation.
var slidesUrl = slides.getUrl();
var html = "<p>Find it in your home Drive folder:</p>"
    + "<p><a href=\"" + slidesUrl + "\" target=\"_blank\">"
    + presentationTitle + "</a></p>";

SpreadsheetApp.getUi().showModalDialog(
  HtmlService.createHtmlOutput(html)
    .setHeight(120)
    .setWidth(350),
    "Created a presentation!"
);

onOpen.js

/**
 * A special function that runs when the spreadsheet is opened
 * or reloaded, used to add a custom menu to the spreadsheet.
 */
function onOpen() {
  // Get the Ui object. 
  var ui = SpreadsheetApp.getUi();
  
  // Create and add a named menu and its items to the menu bar.
  ui.createMenu('Quick formats')
    .addItem('Format row header', 'formatRowHeader')
    .addItem('Format column header', 'formatColumnHeader')
    .addItem('Format dataset', 'formatDataset')
    .addSeparator()
    .addSubMenu(ui.createMenu('Create character sheet')
                .addItem('Episode IV', 'createPeopleSheetIV')
                .addItem('Episode V', 'createPeopleSheetV')
                .addItem('Episode VI', 'createPeopleSheetVI')
                )
    .addToUi();
}

showModalDialog.js

 // Create and display a dialog that tells the user where to
  // find the new presentation.
  var slidesUrl = slides.getUrl();
  var html = "<p>Find it in your home Drive folder:</p>"
      + "<p><a href=\"" + slidesUrl + "\" target=\"_blank\">"
      + presentationTitle + "</a></p>";
  
  SpreadsheetApp.getUi().showModalDialog(
    HtmlService.createHtmlOutput(html)
      .setHeight(120)
      .setWidth(350),
      "Created a presentation!"
  );
  

use this to attach a menu and menu item to the menu bar