javascript - Update/Copy the cell data to another worksheet based on ID match

Question

I'm working on updating the data from Work Sheet 1 to Work Sheet 2 based on Unique ID Match

E.g. As seen in the screenshot of my Work Sheet 1, Which contains the Unique ID. What I want to do is, When I update the Status SOLD in worksheet 1 then I want the Work Sheet 2 to be updated as SOLD that matches the ID (on Edit).

On my search, I found out this answer Click Here was close to my expectation but It's not working for some reason,

Can you help me in this regard? Thanks in Advance.

question from:https://stackoverflow.com/questions/65880885/update-copy-the-cell-data-to-another-worksheet-based-on-id-match

Answer 1

I believe your goal as follows.

• You have 2 Google Spreadsheets like "WorkSheet 1" and "WorkSheet 2".
• You want to run the script when the column "D" of the specific sheet in "WorkSheet 1" is edited to Sold in the cell.
• When the script is run, you want to check the ID of the column "A" of edited row, and want to put the value of Sold to the column "D" of the specific sheet in "WorkSheet 2". In this case, you want to put the value to the same row with the ID in the column "A" of the specific sheet in "WorkSheet 2".
• You want to achieve this using Google Apps Script.

In this case, it is required to use 2 Google Spreadsheets. By this, SpreadsheetApp.openById is used. So it is required to use the installable trigger of OnEdit. The sample script is as follows. In order to search the ID at "WorkSheet 2", TextFinder is used.

Unfortunately, from On my search, I found out this answer Click Here was close to my expectation but It's not working for some reason,, I couldn't understand about your tested script. So in this answer, I proposed a sample script.

Sample script:

Please copy and paste the following script to the script editor of "WorkSheet 1" and please install the installable trigger of OnEdit to the function of installedOnEdit(). And then, please set the variable of spreadsheetIdOfWorksheet2, and each sheet name of "WorkSheet 1" and "WorkSheet 2".

function installedOnEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (!(sheet.getSheetName() == "Sheet1" && range.getRow() > 1 && range.getColumn() == 4 && range.getValue() == "Sold")) return;
  const srcId = range.offset(0, -3).getValue();
  const spreadsheetIdOfWorksheet2 = "###"; // Please set the Spreadsheet ID of "WorkSheet 2" here.
  const worksheet2 = SpreadsheetApp.openById(spreadsheetIdOfWorksheet2).getSheetByName("Sheet1");
  const dstValues = worksheet2.getRange("A1:A" + worksheet2.getLastRow()).createTextFinder(srcId).findAll();
  if (dstValues.length > 0) {
     worksheet2.getRangeList(dstValues.map(r => r.offset(0, 3).getA1Notation())).setValue("Sold");
  }
}

• When you use this script, please edit the column "D" of the specific sheet in "WorkSheet 1" to Sold. By this, the script is run and the value of Sold is put to the same row with the same ID in the specific sheet in "WorkSheet 2".
• In this sample script, the sheet name of the specific sheet in both Google Spreadsheets is Sheet1. So please modify this for your actual situation.

Note:

• This sample script is for your provided the Spreadsheets of the sample images. So when the structure of each sheet is difference from your actual situation, the script might not work. So please be careful this.

References:

• Installable Triggers
• Class TextFinder