google-sheetsgoogle-apps-scriptimporttriggers

Why can't an onEdit checkbox click trigger an import in Google Apps Script?


I'm working on an interlinked raw materials inventory spreadsheet and a brew log, and I want to be able to pull the current inventory into the brew log sheet on a click.

I've got the importRawMat function connected to the button "Boop", which works just fine to import the data I need, but I don't have any success linking it to a checkbox click with onEdit, despite onEdit triggering the 'Clear' function just fine.

I'm wondering if it's related to Simple Triggers being restricted around services that require authorisation? If so, how can I get a trigger on this function that will be accessible through the Google Sheets App, as buttons and custom menus don't work through there.

See below for link and code:

https://docs.google.com/spreadsheets/d/1qrDVIEpv7DeqGlIq8YUBTyn2DWfNbsoSm77I30rd9Jw/edit?gid=411516577#gid=411516577


    function onEdit(e) {
      if (!e.value) return
      if (e.value !== "TRUE") return
      if (e.range.rowStart !== 1) return
      if (e.range.columnStart !== 1) return
      if (e.range.getSheet().getName() !==  "Brew") return

      importRawMat()

    }

    function onEdit(e) {
      if (!e.value) return
      if (e.value !== "TRUE") return
      if (e.range.rowStart !== 2) return
      if (e.range.columnStart !== 3) return
      if (e.range.getSheet().getName() !==  "Brew") return

     Clear()
      e.range.uncheck()

    }

    function Clear() {
      var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.getRangeList(['A1', 'A1']).activate()
      .uncheck();
    };

    function importRawMat() {

      var originsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1GOmX04m8OC1dFWFdhBonLTt07SImkq6gGx5l9l6SSXA/edit?gid=411516577#gid=411516577').getSheetByName('Malt');
      var origindata = originsheet.getDataRange().getValues();
      var destinationsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Blank');
      destinationsheet.clearContents();destinationsheet.getRange(1,1,origindata.length,origindata[0].length).setValues(origindata);
  
    }


Solution

  • Modification points:

    When these points are reflected in your showing script, it becomes as follows.

    Modified script:

    Please do the following flow.

    1. Please remove 2 onEdit functions from your project.
    2. Please copy and paste the following script to the project.
    3. Please install the OnEdit trigger to installedOnEdit. Ref

    By this flow, when you check the checkbox of "A1" or "C2" of the Brew sheet, the function installedOnEdit is automatically run by the installable OnEdit trigger.

    function installedOnEdit(e) {
      const { range } = e;
      const sheet = range.getSheet();
      if (range.isBlank() || !range.isChecked() || sheet.getName() != "Brew") return;
      if (range.rowStart == 1 && range.columnStart == 1) { // or  if (range.getA1Notation() == "A1") {
        importRawMat();
      } else if (range.rowStart == 2 && range.columnStart == 3) { // or  else if (range.getA1Notation() == "C2") {
        Clear();
        range.uncheck();
      }
    }
    

    Note:

    Reference: