tanaikech

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.

1. When an user edited a single "A1" cell, e of onEdit(e) shows hoge for e.oldValue and fuga for e.value.
2. When an user edited the "A1:A2" multiple cells, e.oldValue and e.value of onEdit(e) are not shown anything.
3. 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.

rudimusmaximus

How To Get Started In Google Apps Script

Initially intended to extend G Suite apps, I like to think of Google Apps Script as a gateway to more kinds of development. Think of it as workflow glue and the power of programming that can interract with Google Apps and external APIs too!

Purpose

Provide a living document for whenever someone asks, " so, how do i get started with Google Apps Script?".

Working Outline

Just the orgainizing principles and some key links.

1 Starting point: Good Things to Keep in Mind

Scripts are 'bound' to a container like sheets, docs, slides or forms. These can be accessed from the containing doc and opened say in sheets by going to the menu Tools > Script editor. Scripts can also be standalone for addons or web apps. Your script home page is a dashboard found here script.google.com. The help link there will get you to an explanation of the dashboard.

Spencer-Easton

function exportSpreadsheet() {
 
  //All requests must include id in the path and a format parameter
  //https://docs.google.com/spreadsheets/d/{SpreadsheetId}/export
 
  //FORMATS WITH NO ADDITIONAL OPTIONS
  //format=xlsx       //excel
  //format=ods        //Open Document Spreadsheet
  //format=zip        //html zipped          
  

erickoledadevrel

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Testing')
  .addItem('Show Sidebar', 'showSidebar')
  .addToUi();
}

function showSidebar() {
  var sidebar = HtmlService.createHtmlOutputFromFile('Sidebar')
  .setSandboxMode(HtmlService.SandboxMode.IFRAME)
  .setTitle('Sidebar');

mogsdad

/** * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * AddonUtilities.gs
 *
 * A collection of general Google-Apps-Script utilities that are useful for
 * development of Add-ons.
 * 
 * From: gist.github.com/mogsdad/39db2995989110537868
 *
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

mogsdad

Google Apps Script Spreadsheet Utilities and Custom Functions#

These utilities are grouped into related files, for simpler copy & paste to your scripts.

ConvertA1.gs

A couple of helper functions to convert to & from A1 notation.

cellA1ToIndex( string cellA1, number index )

labnol

Learning Google Apps Script

Find the best resources for learning Google Apps Script, the glue that connects all GSuite services including Gmail, Google Drive, Calendar, Google Sheets, Forms, Maps, Analytics and more.

A good place to learn more about Google Apps Script is the official documentation available at developers.google.com. Here are other Apps Script resources that will help you get up to speed.

1. Google Apps Script Code Samples by Amit Agarwal
2. Google Apps Script Development - Create Google Apps Script projects locally inside VS Code - video tutorial
3. Awesome Google Scripts by Amit Agarwal
4. Google Developer Experts - Follow Apps Scr

peterherrmann

//load configuration details and start logging - creates and sets up sheets the first time they are run
var CONFIG_SPREADSHEET_KEY = '<ssid_goes_here>';
var Config = SettingsManager.load(CONFIG_SPREADSHEET_KEY); //Add Mafviu9bMfg9xVu21LGfpWnHAGDwXQ1CH in Resources > Libraries
Logger = BetterLog.useSpreadsheet(Config['logSpreadsheetId'].value);//Add MYB7yzedMbnJaMKECt6Sm7FLDhaBgl_dE in Resources > Libraries

// trigger this function
function outerLoop() {
  try {
    // to calc elapsed time
    var isOverMaxRuntime = false,