How to use an installable trigger to get SpreadsheetApp.openById(id) to work, by calling it from a c-CodePudding

I have a sheet where users download the latest version. I want them to be able to import their 'logs' (2 sheets with ranges) from whatever version they last had by copying the spreadsheet id from a box and pasting it into a different box and pressing a button that executes the installable trigger script.

I found a few things on here, but I'm unsure of how to call or parse the pasted id from the cell, as the example below (which I found here) seems wrong since it just defines a variable twice.

function setTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("importlogs").forSpreadsheet(ss).onEdit().create();
}

//variables
var id = "123456789abcdefg";
var sheet = "LOG WIZARD";
var cells = "M11:AB13";
var range = SpreadsheetApp.openById(id).getSheetByName(sheet).getRange(cells);
var id = range.getSheet().getParent().getId();

//custom function to import logs
function importlogs() {

//Source sheet from which to import from
var is = SpreadsheetApp.openById(id)
var sheet1i = is.getSheetByName("BUDGET LOG");
var sheet2i = is.getSheetByName("LOG");

//Current sheet from which to export to
var xs = SpreadsheetApp.getActiveSpreadsheet();
var sheet1x = xs.getSheetByName("BUDGET LOG");
var sheet2x = xs.getSheetByName("LOG");

//Copy and paste contents of import Budget Log sheet to export Budget Log sheet
sheet1i.getRange("A3:AO").copyTo(sheet1x.getRange(sheet1x.getLastRow() 1,1,1,7), {contentsOnly:true});

//Copy and paste contents of import Log sheet to export Log sheet
sheet2i.getRange("A3:O").copyTo(sheet2x.getRange(sheet2x.getLastRow() 1,1,1,7), {contentsOnly:true});

}

CodePudding user response：

When the trigger fires, you can examine the event object e to find which cell was edited. If it is the cell you are interested in, you can call the import function with the value that was entered, like this:

/**
* Installable trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The installable "on edit" event object.
*/
function runImportLogs(e) {
  if (!e) {
    throw new Error(
      'Please do not run the runImportLogs(e) function in the script editor window. '
        'It runs automatically when you hand edit the spreadsheet.'
    );
  }
  if (!e.value
    || e.range.getA1Notation() !== 'M11'
    || e.range.getSheet().getName() !== 'LOG WIZARD') {
    return;
  }
  importLogs_(e.value);
}

To use the passed value in the import function, declare it as an argument, like this:

/**
* Imports logs.
*
* @param {Object} sourceSsId The ID of the spreadsheet where to read data from.
*/
function importLogs_(sourceSsId) {
  try {
    const sheetNames = ['BUDGET LOG', 'LOG',];
    const sourceSs = SpreadsheetApp.openById(sourceSsId);
    const sourceSheets = sheetNames.map(sheetName => sourceSs.getSheetByName(sheetName));
    const targetSs = SpreadsheetApp.getActive();
    const targetSheets = sheetNames.map(sheetName => targetSs.getSheetByName(sheetName));
    sourceSheets.forEach((sheet, index) => {
      const data = sheet.getRange('A3:AO').getValues();
      appendRows_(targetSheets[index], data);
    });
  } catch (error) {
    SpreadsheetApp.getActive().toast(error.message);
    throw error;
  }
}

For this to work, you will need to paste the appendRows_() utility function in the script project.

Finally, when you are setting up the trigger, it usually makes sense to first delete any duplicate triggers that may still be hanging around:

function setTrigger() {
  const ss = SpreadsheetApp.getActive();
  const triggers = ScriptApp.getUserTriggers(ss);
  triggers.forEach(trigger => {
    if (trigger.getEventType() === ScriptApp.EventType.ON_EDIT) {
      ScriptApp.deleteTrigger(trigger);
    }
  });
  ScriptApp.newTrigger('runImportLogs').forSpreadsheet(ss).onEdit().create();
}

CodePudding user response：

function setTrigger() {
  if (ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == "importLogs").length == 0) {
    ScriptApp.newTrigger("importlogs").forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
  }
}

function importlogs() {
  var id = "123456789abcdefg";
  var is = SpreadsheetApp.openById(id)
  var sheet1i = is.getSheetByName("BUDGET LOG");
  var sheet2i = is.getSheetByName("LOG");
  var xs = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1x = xs.getSheetByName("BUDGET LOG");
  var sheet2x = xs.getSheetByName("LOG");
  sheet1i.getRange("A3:AO").copyTo(sheet1x.getRange(sheet1x.getLastRow()   1, 1), { contentsOnly: true });
  sheet2i.getRange("A3:O").copyTo(sheet2x.getRange(sheet2x.getLastRow()   1, 1), { contentsOnly: true });
}