Google sheet script to update cell value in another sheet

Question

I'm trying to use a code I found in another topic but so far I failed. I'm trying to find the corresponding row and update the last column in another google spreadsheet after updating the first column of another spreadsheet.

When the user selects "ready" in ColC of spreadsheet X, I need to look up the ID value in ColB on another sheet (Y). Then I need to access spreadsheet Y and find the row that contains that same ID. Access the last column or columnC (3) and change the cell value to "ready".

Here is what I have so far but I get the error:

TypeError: Cannot read properties of undefined (reading 'range')

Can anyone help me fix this?

    function onEdit(e){
    
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var s = ss.getActiveSheet();
      if( s.getName() == "Studio"){
    
           if ( (e.range.getColumn() == 2.0) && (e.range.getValue() == "ready") ){
    
            var nextCell = e.range.offset(0, -1);
            var buybackId = nextCell.getValue();
          
            var ss2 = SpreadsheetApp.openById('1bbNuH_GXbmNwMHk7Kjo0zAhD-QGwQqiva8_HJc3mgFY');
    
            var sheet = ss2.getSheetByName("Status"); 
    
            var data = sheet.getDataRange().getValues();
            for(var i = 0; i<data.length;i++){
              if(data[i][1] == buybackId){ 
    
                sheet.getRange((i+1), 3).setValue("ready");
              }
            }
          }
      }
    }

The table structure looks like this:

Answer 1

In the 1st section of your question, you say I'm trying to find the corresponding row and update the last column in another google spreadsheet after updating the first column of another spreadsheet.. But, from your 2nd section, it seems that you wanted to run the script by editing column "B" like ready. By the way, about When the user selects "ready" in ColC of spreadsheet X,, your selects is to edit?

So, when column "B" in "Studio" sheet on the active Spreadsheet is edited to like ready, you want to update column "C" of "Status" sheet in another Spreadsheet by checking the ID of column "B".

If my understanding is correct, how about the following modification?

Modification points:

• In order to use SpreadsheetApp.openById, it is required to use the installable OnEdit trigger.
• About your current error of TypeError: Cannot read properties of undefined (reading 'range'), I'm worried that you might have directly run the function onEdit with the script editor. In that case, such an error occurs because of no event object.

When these points are reflected in your script, how about the following modification?

Modified script:

Please copy and paste the following script to the script editor of Spreadsheet. And, please install OnEdit trigger to installedOnEdit. When you use this script, please edit the column "B" of "Studio" sheet of the active Spreadsheet. By this, the script is run. And, please set your destination Spreadsheet ID to const ss = SpreadsheetApp.openById('###');.

function installedOnEdit(e) {
  const { range } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != "Studio" || range.columnStart != 2) return;
  const [id, status] = range.offset(0, -1, 1, 2).getDisplayValues()[0];
  const ss = SpreadsheetApp.openById('###'); //  Please set the destination Spreasheet ID.
  const dstSheet = ss.getSheetByName("Status");
  const r = dstSheet.getRange("B2:B" + dstSheet.getLastRow()).createTextFinder(id).matchEntireCell(true).findNext();
  if (!r) return;
  r.offset(0, 1).setValue(status);
}

• When this script is run by the installable OnEdit trigger, the above goal can be achieved.

Note:

• If you want to copy all values from the source sheet to the destination sheet, how about the following sample script? In this case, you can directly run the script with the script editor.

  function sample() {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Studio");
    const obj = sheet.getRange("A2:B" + sheet.getLastRow()).getDisplayValues().reduce((o, [a, b]) => (o[a] = b, o), {});
    const ss = SpreadsheetApp.openById('###'); //  Please set the destination Spreasheet ID.
    const dstSheet = ss.getSheetByName("Status");
    const dstRange = dstSheet.getRange("B2:C" + dstSheet.getLastRow());
    const dstValues = dstRange.getValues().map(([b, c]) => [obj[b] || c]);
    dstRange.offset(0, 1, dstValues.length, 1).setValues(dstValues);
  }
  

Note:

• About the 1st script, when you directly run the script, an error like TypeError: Cannot destructure property 'range' of 'e' as it is undefined. occurs. Please be careful about this. The 1st script is automatically run with the installable OnEdit trigger.

• By the way, when you use the 1st script, please rename your onEdit function name. Because when you didn't rename it, when the cell is edited, both functions installedOnEdit and onEdit are run. Please be careful about this.

Reference:

• Installable Triggers

Added:

About your new question of How to modify it if I need to edit status in column C instead of column B in "Production sheet"? and I need it to be triggred by edit in column C sending data from column C., please modify the above script as follows.

Modified script:

function installedOnEdit(e) {
  const { range } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != "Studio" || range.columnStart != 3) return; // Modified
  const [id, , status] = range.offset(0, -2, 1, 3).getDisplayValues()[0]; // Modified
  Browser.msgBox([id, , status])
  const ss = SpreadsheetApp.openById('###'); // Please set your Spreadsheet ID of destination spreadsheet.
  const dstSheet = ss.getSheetByName("Status");
  const r = dstSheet.getRange("b2:b" + dstSheet.getLastRow()).createTextFinder(id).matchEntireCell(true).findNext();
  if (!r) return;
  r.offset(0, 4).setValue(status);
}