I have a script that runs on edit basis and its trigger is installable. When it's run, it copies a row into a another file. I would like to have an error message pop up in case that didn't go through for some reason, so that the user knows it requires additional attention.

Right now, the error I see in the executions logs says it's trying to edit a protected range in the destination sheet. I've added this try...catch as follows, but nothing pops up.

function myfunction(e) {
  try {
    const startRow = 2;
    const targetColumn = 11;
    const ws = "Data"

    //get modified row and column
    const row = e.range.getRow();
    const col = e.range.getColumn();
    const s = e.source.getActiveSheet();
    const cellValue = s.getActiveCell().getValue();
    const salesPapeline = e.source.getActiveSheet().getRange(row, 1).getValue();
    const branch = e.source.getActiveSheet().getRange(row, 13).getValue();

    if (e.source.getActiveSheet().getName() === ws && col === 2 && row >= startRow) {
      const timeStamp = new Date();

      //This checks if the Job/Lead is to be transferred to Branch 2
      if (salesPapeline == "4. Convert" && cellValue == "2. Branch 2"
        && e.source.getActiveSheet().getRange(row, 12).getValue() == "TRANSFERRED") {
        const jobTagValue = e.source.getActiveSheet().getRange(row, 11).getValue();

        let rowData = e.source.getActiveSheet().getRange(row, 1, 1, 12).getValues();
        let newItems = [];
        let jobTag = rowData[0][10];
        let name = rowData[0][3];
        let company = rowData[0][4];
        let clientNotes = rowData[0][7];
        let startDate = rowData[0][9];
        newItems.push(jobTag, '3. Current needs', '', name, '', company, clientNotes, '', '', '', '', '', startDate)
        e.source.getActiveSheet().getRange(row, 12).setValue('TRANSFERRED');
        e.source.getActiveSheet().getRange(row, 13).setValue('2. Branch 2');

        const salesSheet = SpreadsheetApp.openById(BRANCH2_FILE_ID);
        const destSheet = salesSheet.getSheetByName('[MGT-use] Recruiting Funnel');

        if (newItems.length > 0) {
          const destRng = destSheet.getRange(destSheet.getLastRow()   1, 1, 1, 13);
          destRng.setValues([newItems]);
        } else {
          return;
        }
      }
    }
  } catch (e1) {
    SpreadsheetApp.getUi().alert(e1.message);
  }
}

CodePudding user response：

The trigger onEdit() handles errors silently. You can see the errors messages (and logs) in Executions section only:

For debugging, instead of (or along with) console.log(), you can use the spreadsheet.toast() tool:

• toast(msg)

CodePudding user response：

This checks the range that was written to see if it is blank

I also stuck some debugging flags in there in the event we need to go further into the process. But I don't really see anything wrong with it except that you could make better use of the event object and reduce execution time.

function myfunction(e) {
  e.source.toast('entry');
  try {
    const sh = e.range.getSheet();
    if (sh.getName() == "Data" && e.range.columnStart == 2 && e.range.rowStart >= 2) {
      e.source.toast('Flag1');
      const r = sh.getRange(e.range.rowStart, 1, 1, 12).getValues().flat();
      if (r[0] == "4. Convert" && e.value == "2. Branch 2" && r[11] == "TRANSFERRED") {
        e.source.toast('Flag2');
        let newItems = [];
        newItems.push(r[11], '3. Current needs', '', r[3], '', r[4], r[7], '', '', '', '', '', r[9]);
        sh.getRange(e.range.rowStart, 12).setValue('TRANSFERRED');
        sh.getRange(e.range.rowStart, 13).setValue('2. Branch 2');
        const ssh = SpreadsheetApp.openById(BRANCH2_FILE_ID);
        const dsh = ssh.getSheetByName('[MGT-use] Recruiting Funnel');
        if (newItems.length > 0) {
          e.source.toast('Flag3');
          const wr = dsh.getLastRow()   1
          const drg = dsh.getRange(wr, 1, 1, 13);
          drg.setValues([newItems]);
          SpreadsheetApp.flush();
          if(drg.isBlank()) {
            SpreadsheetApp.getUi().alert("It did not go through");//warns you if the row that you just wrote to is blank.
          }
        } 
      }
    }
  } catch (e) {
    console.log(e.message);
  }
}

If you wish to share some data in a table I wouldn't mind looking at it. You may need to explain how the sheet works as well.

Note: I do not follow links to other google accounts.