Testing Spreadsheet for anyone that wants to test on it

I have a spreadsheet with 4 charts (just line charts) And each chart changes the data based on a dropdown, I also calculate the min and max i want from the data in a couple of cells next to it, the code basically is just trying to update the charts min and max when i edit the cell with the dropdown, but I cant find where is the error.

function onEdit(e) {

  //run dynamic charts switch-case function
  const ss= SpreadsheetApp.openById("Sheet-ID");
  const sheet = ss.getSheetByName("Analysis2.0");
  var range = e.range;
  var target = range.getA1Notation();
  const charts = sheet.getCharts();


  for (var i in charts) {
    let chart = charts[i];
    const chartName = chart.getOptions().get("title");

    if (target == "H18") {
      switch (chartName) {
        case "#0 Dynamic e1RM Chart":
          Utilities.sleep(200);
          var vaxis0min = sheet.getRange("E19").getValue();
          var vaxis0max = sheet.getRange("F19").getValue();
          chart = chart.modify()
            .setOption('vAxis', { minValue: vaxis0min })
            .setOption('vAxis', { maxValue: vaxis0max })
            .build();
          sheet.updateChart(chart);
          break;
      }
    }
    if (target == "X18") {
          switch (chartName) {
            case "#1 Dynamic e1RM Chart":
              Utilities.sleep(200);
              var vaxis1min = sheet.getRange("U19").getValue();
              var vaxis1max = sheet.getRange("V19").getValue();
              chart = chart.modify()
                .setOption('vAxis', { minValue: vaxis1min })
                .setOption('vAxis', { maxValue: vaxis1max })
                .build();
              sheet.updateChart(chart);
              break;
          }
        }
    if (target == "AN18") {
          switch (chartName) {
            case "#2 Dynamic e1RM Chart":
              Utilities.sleep(200);
              var vaxis2min = sheet.getRange("AK19").getValue();
              var vaxis2max = sheet.getRange("AL19").getValue();
              chart = chart.modify()
                .setOption('vAxis', { minValue: vaxis2min })
                .setOption('vAxis', { maxValue: vaxis2max })
                .build();
              sheet.updateChart(chart);
              break;
          }
        }
    if (target == "BD8") {
          switch (chartName) {
            case "#3 Dynamic e1RM Chart":
              var vaxis3min = sheet.getRange("BA19").getValue();
              var vaxis3max = sheet.getRange("BB19").getValue();
              chart = chart.modify()
                .setOption('colors', ['#990000'])
                .setOption('vAxis', { minValue: vaxis3min })
                .setOption('vAxis', { maxValue: vaxis3max })
                .build();
              sheet.updateChart(chart);
              break;
          }
    }
  }
}

CodePudding user response：

Issue:

I installed an On Edit Trigger and now it works however the executions log now reflects 2 executions every time it triggers, one failed and one successful, any idea if this is normal? if not what should I do?

the failed execution says; Exception: You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets at onEdit(onEdits:4:28)

Simple triggers cannot access services that require authentication. Since SpreadsheetApp.openById requires authentication, the function fails.

Solution:

If your script is bound to the spreadsheet, replace SpreadsheetApp.openById by SpreadsheetApp.getActive(), so that the script doesn't require authenticated access beyond the current document.

Otherwise, just remove the simple trigger (rename your function so that only the installed trigger runs - you might have to reinstall the trigger after changing the function name).

Reference:

• Restrictions