Google Sheets code: Resolving permission issues and data placement errors

Question

I'm writing my first code using Google Script. I'm having trouble with my code, it doesn't have the required permissions to access Google Sheets. When trying to execute the code I get the error "Exception: You do not have permission to call SpreadsheetApp.openById". This code is designed to modify and retrieve data from a Google Sheets spreadsheet.

In addition to the permissions issue, there is a specific behavior in the code that needs to be addressed. The code is designed to transfer information to a specific row in a Google Sheets spreadsheet, but is currently transferring data to a location outside of the spreadsheet. For example, if there is free space available in row 9 of the spreadsheet, the code will incorrectly transfer the information to another location outside the table without formatting or structure. I've attached my code below

function onEdit(e) {
  var sourceSheet = e.source.getActiveSheet();
  var editedRange = e.range;
  var column = editedRange.getColumn();
  var row = editedRange.getRow();
  
  if (column > 1 && row > 1) {
    var technicianNameCell = sourceSheet.getRange(row, 13);
    var technicianName = technicianNameCell.getValue();
    var dataValues = [];
    
    var columnsToCopy = [1, 2, 3, 4, 5, 6, 7, 11, 12]; // A, B, C, D, E, F, G, J
    
    for (var i = 0; i < columnsToCopy.length; i++) {
      var value = sourceSheet.getRange(row, columnsToCopy[i]).getValue();
      dataValues.push(value);
    }
    
    var targetSpreadsheetIds = {
      "technicianName": {
        "spreadsheetId": "spreadsheetId",
        "sheetName": "sheetName"
      },
      "technicianName": {
        "spreadsheetId": "spreadsheetId",
        "sheetName": "sheetName"
      },
      "technicianName": {
        "spreadsheetId": "spreadsheetId",
        "sheetName": "sheetName"
      }
    };
    
    var targetSpreadsheet = targetSpreadsheetIds[technicianName];
    
    if (targetSpreadsheet) {
      var targetSheet = SpreadsheetApp.openById(targetSpreadsheet.spreadsheetId).getSheetByName(targetSpreadsheet.sheetName);
      var lastRow = targetSheet.getLastRow();
      var firstEmptyRow = lastRow + 1;
      
      var targetRange = targetSheet.getRange(firstEmptyRow, 1, 1, dataValues.length);
      targetRange.setValues([dataValues]);
    }
  }
}

I'm trying to implement a code to modify and retrieve data from a Google Sheets spreadsheet. I want the code to have the necessary permissions to access the spreadsheet and perform the required actions. Specifically, I want the code to fill the empty spaces in the spreadsheet with the transferred information, while preserving the format and structure of the table.

However, the actual result is that the code encounters permission problems. "Exception: You do not have permission to call SpreadsheetApp.openById" error occurs when executing the code. Furthermore, the transmitted information is placed outside the table and has no format or structure.

Data placement in Google Sheets code

Okay, that's it. The code I'm using is very good at finding blank spots in tables. The only problem is that it ends up placing the data outside of the actual table I have. You see, the code thinks the format in the table is a complete row, so it's not placing the data exactly where I want it to be in the row. Enter image description here

The code I have now

function myEditTrigger(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Sheet Name" && e.range.columnStart > 1 && e.range.rowStart > 1) {
    const [a, b, c, d, e, f, g, , , k, l, m] = sh.getRange(e.range.rowStart, 1, 1, sh.getLastColumn()).getValues()[0];
    let vs = [a, b, c, d, e, f, g, k, l];
    var tObjs = {
      "techname": {
        "spreadsheetId": "spreadsheetId",
        "sheetName": "sheetName"
      },
   
    };
    var tObj = tObjs[m];
    if (tObj) {
      var tsh = SpreadsheetApp.openById(tObj.spreadsheetId).getSheetByName(tObj.sheetName);
      tsh.getRange(tsh.getLastRow() + 1, 1, 1, vs.length).setValues([vs]);
    }
  }
}

Answer 1

Use installable triggers and rename trigger functions, do not use onEdit

function onMyEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Sheet Name" && e.range.columnStart > 1 && e.range.rowStart > 1) {
    const [a,b,c,d,e,f,g,,,,k,l,m] = sh.getRange(e.range.rowStart, 1 , 1, sh.getLastColumn()).getValues()[0];
    let vs = [a,b,c,d,e,f,g,k,l];
    var tObjs = {"technicianName": {"spreadsheetId": "spreadsheetId","sheetName": "sheetName" },...};//Hard code the objs or make them global
    var tObj = tObjs[m];
    if (tObj) {
      var tsh = SpreadsheetApp.openById(tObj.spreadsheetId).getSheetByName(tObj.sheetName);
      tsh.getRange(tsh.getLastRow() + 1, 1, 1, vs.length).setValues(vs);
    }
  }
}