nkriddock

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 )

nkriddock

function onFormSubmit(e) {
  const senderVisibleName = 'Google Form script - Weekly update';
  const sheetName = 'Recipients';
  
  //  var form = FormApp.getActiveForm(); // to test without actual events
  var form = e.source;
  var formUrl = form.getPublishedUrl();
  var destinationId = form.getDestinationId();
  var sheet = SpreadsheetApp.openById(destinationId).getSheetByName(sheetName);

nkriddock

function editForm() {

  
  var form = FormApp.openById('<YOUR_FORM_ID_GOES_HERE>');
  
  // Following line just logs form title in logger. 
  // Helps in troubleshooting. :)
  Logger.log('Form Title is '+ form.getTitle()); 
  
  // Gets all items inside the form.   

nkriddock

function irregularity(e){
  
  var values = e.values;
  
  var sheet = e.source.getActiveSheet();
  var activeCell = sheet.getActiveCell();
  var row = activeCell.getRow();
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formUrl = ss.getFormUrl();

nkriddock

// Use this code for Google Docs, Forms, or new Sheets.
function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .createMenu('Dialog')
      .addItem('Generate Active Mon List', 'GenActiveMonsterList')
      .addToUi();
}


// Pull data from an external spreadsheet and generate HTML table in popup dialog box

nkriddock

function onFormSubmit(e) {
  var form = FormApp.openById('1Z65umQSZ2drz3MvTWCWlwP4-ySeoB5D7RvX1LtusnEU');
  var formResponses = form.getResponses();
  var latestResponse = formResponses.length - 1;
  var formResponse = formResponses[latestResponse];
  
  var email = formResponse.getRespondentEmail();
  
  var items = formResponse.getItemResponses();

nkriddock

var moment = Moment.load();

var GLOBAL = {
  formId : "GOOGLE FORM ID", 
  calendarId : "GOOGLE CALENDAR ID",
  formMap : {
    eventTitle: "FORM FIELD TITLE NAME",
    startTime : "FORM FIELD START TIME NAME",
    description: "FORM FIELD DESCRIPTION NAME",
    

nkriddock

var form = FormApp.openById('1lzfKNxJsw4q6MH3L3m9F6ryyr2xPEFLScaqOUKMf6kQ')                               

function onFormSubmit(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName("INCOMPLETE") 
  var responses = [e.values.slice(0,e.values.length)]
    
  // Create new row in target sheet and set to target
  targetSheet.insertRows(3)
  

nkriddock

function onFormSubmit(e) {
	var url = "https://api.pushbullet.com/v2/pushes";
	var responses = ' ';
	var form = FormApp.openById('<FormID>'); // https://docs.google.com/forms/d/FormID/edit
	var formResponses = form.getResponses();
	var formResponse = formResponses[formResponses.length - 1];
	var itemResponses = formResponse.getItemResponses();
	var customerNameresponse = itemResponses[0];
	var mrrresponse = itemResponses[2];
	responses = responses + customerNameresponse.getResponse();

nkriddock

function updateForm(){
  // call your form and connect to the drop-down item
  var form = FormApp.openById("FORM_ID");
   
  var namesList = form.getItemById("id_number").asListItem(); // data-observe-id="id_number"
  
  // identify the sheet where the data resides needed to populate the drop-down
  var ss = SpreadsheetApp.getActive();
  var names = ss.getSheetByName("Sheet1");