Google Sheets - Update Sheet 1 with data from Sheet 2 if a value on both sheets match

Question

I have two sheets on one spreadsheet:

https://docs.google.com/spreadsheets/d/1ahtB9M6rl6aHmj51ZlP_qDqyUwhpsSVg1r16BafcKIo/edit?usp=sharing

Using Google Apps Script, I want to update a value in column C on the "Report" sheet, with a value from column E on the "New Data" sheet, IF the values in the ID column on each sheet match.

In production, I'm expecting there to be be multiple unique matches. How would I best do this?

As a simple example, in the mock sheets below, I would like the value in the "STATE" column of the Report Sheet where the row ID is 12 to update from "Queued" to "Won".

Report Sheet

ID | STATE

12 | Queued

34 | Lost
New Data Sheet

ID | STATE

56 | Lost

12 | Won

Cool project. Please let us know if you have a question about something. — Antoine Colson
Hi Antoine, thanks! I realised I hadn't explicitly asked a question (just edited). I'm hoping to find out how best to do this? I've tried a few methods but to no avail. — Robert Samarji
hey I see you are getting downvotes, here is a guide to asking better questions. applies to Stack Overflow and other places. — Antoine Colson
Thanks Antoine! Almost to be expected on my first question I guess... Thanks for the link, I'll check it out! — Robert Samarji

Antoine Colson Antoine Colson · Accepted Answer · 2017-12-14T02:55:55

There was a similar question asked this week.

Here it my suggestion, though you will need to adapt the names of sheets and titles!

function setCommon() {

  var ss = SpreadsheetApp.getActive(),
      compare1 = "", compare2 = "",

      outputSheet = ss.getSheetByName("Sheet1"),
      sourceSheet = ss.getSheetByName("Sheet2"),

      range1 = outputSheet.getDataRange(),
      range2 = sourceSheet.getDataRange(),

      lastCol1 = range1.getNumColumns(),
      lastCol2 = range2.getNumColumns(),

      values1 = range1.getValues(),
      values2 = range2.getValues(),

      // get the range of the titles
      titleSection1 = outputSheet.getRange(1,1,1, lastCol1),
      titleSection2 = sourceSheet.getRange(1,1,1, lastCol2),

      // get the values from the titles
      titles1 = titleSection1.getValues(),
      titles2 = titleSection2.getValues(),

      // get the column # for "ID" and "comment"
      idCol1 = titles1[0].indexOf("ID"),
      idCol2 = titles2[0].indexOf("ID"),
      commentsCol1 = titles1[0].indexOf("comment"),
      commentsCol2 = titles2[0].indexOf("comment");

  // get the IDs from range1
  for (i = 1; i < values1.length; i++) { 
    compare1 = values1[i][idCol1];

    // get the IDs from range2
    for (j = 1; j< values2.length; j++){
      compare2 = values2[j][idCol2];

      // if same ID, change the values array
      if (compare1 == compare2) {
        values1[i][commentsCol1] = values2[j][commentsCol2];
      }
    }
  }
  // set values based on the values array
  range1.setValues(values1);
}

Google Sheets - Update Sheet 1 with data from Sheet 2 if a value on both sheets match

1 Answers