IssueTracker

Hello,

Thank you for the details. This has been forwarded internally.

Best regards

Also getting this, but I can narrow it down to one line.

I have tried different workarounds based on other responses. This is a simplified script, basically, if I comment out setNumberFormat and setNumberFormats then it works, but no matter which one I use, the moment I use it I get the error.

Sep 14, 2020, 7:06:14 PM Error Service Spreadsheets failed while accessing document with id 1w-U7-Zh6bcY9_d0EARN8qes6k72zJy5Q2Nw-70tkYOM.

function onEdit_projections_UsageUnit(e) {
  // the sheet that we are generating the dropdowns on
  const range_check = (row, column) => (column === 2 && 1 <= row && row < 10);

  if (!(
    e.value != null
    // value has changed
    && e.oldValue !== e.value
    && e.source.getSheetName() === SHEET_PROJECTIONS
    && range_check(e.range.getRow(), e.range.getColumn())
  )) {
    return;
  }
  Logger.info('onEdit_projections_UsageUnit activate');
  
  // Option i
  // const sheet = e.range.getSheet();
  // Option ii
  const sheet = e.source.getSheetByName(SHEET_PROJECTIONS);
  
  // const sheet = SpreadsheetApp.getActiveSpreadsheet();
  
  Logger.info(`onEdit_projections_UsageUnit sheet ${sheet.getSheetName()}`);
  
  const mapRange = sheet.getRange('D3:9');
  const mapColumns = mapRange.getNumColumns();
  Logger.info(`onEdit_projections_UsageUnit mapRange ${mapRange.getNumRows()}:${mapColumns}`);
  
  const modelRange = sheet.getRange('C3:C9');  //mapRange.offset(0, -1, mapRange.getNumRows(), 1);
  const models = modelRange.getValues();
  
  Logger.info(`onEdit_projections_UsageUnit models (${models.join(', ')})`);
  
  for (const [index, [model, ]] of models.entries()) {
    Logger.info(`onEdit_projections_UsageUnit each:${index} model:${model}`);
    
    if (model === '') { 
      // wipe out formatting.
    } else if (true) {
      
      // option A:
      //const rowRange = mapRange.offset(index, 0, 1, mapColumns);
      // option B:
      const rowRange = sheet.getRange(`D${3 + index + 1}:${3 + index + 1}`);
      
      Logger.info(`onEdit_projections_UsageUnit each:${index} range:${rowRange.getA1Notation()}`);
      
      // option 1:
      //rowRange.setNumberFormat(`0.## blah`);
      // option 2:
      const sploosh = [new Array(mapColumns).fill(`0.## blah`)];
      rowRange.setNumberFormats(sploosh);
      Logger.info(sploosh);
    }
  }

  Logger.info(`onEdit_projections_UsageUnit done done`);
}