Google Sheet App Script: Match two values from one sheet to another sheet and then set a value if they match

Viewed 690

very new to app script but well versed in formulas and so up-skilling.

For reference I've made a test sheet HERE

My script has been a Frankenstein of tutorials but essentially I'm trying to match the name and date from sheet 2 to the data in sheet 1 and if they match then in column "C" I'd like to setValue as "sent"

Here is what I've tried so far:

    function sendReport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var clientName = ss.getRange ("Sheet2!B1");
  var testDate = ss.getRange ("Sheet2!B2")
  var destSheet = ss.getSheetByName("Sheet1");
  var range = destSheet.getDataRange();
  var values = range.getValues();
  for(var i = 1; i < values.length; i++){

    if (values[i][1] == clientName
        && values[i][2] == testDate) {
      values[i][3] = Yes; 
}
range.setValues(values)
  }

The actual sheet is quite large and it seems to go through each row at a time checking as the script takes a long time to run but then it's not adding the "Yes" value into the column to confirm the report has been sent.

Would really appreciate any help.

1 Answers

You can use the following script

function sendReport() {
  Logger.log("Starting script...");
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // you need to get the actual sheets of the array
  // you can use array destructuring (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Destructuring_assignment) for this
  const [sheet1, sheet2] = ss.getSheets(); // getSheets() returns an array of sheets
  // get ranges from sheet 2
  // no need to provide the sheet name as a parameter, because we're calling it on sheet2 already
  const clientName = sheet2.getRange("B1").getDisplayValue(); // you need to get the display value, not just the range
  const testDate = sheet2.getRange("B2").getDisplayValue(); 

  // start in row 2, column 1 and get all rows expect the first (=> last - 1) and get 3 columns
  const range = sheet1.getRange(2, 1, sheet1.getLastRow() - 1, 2);
  // use forEach() and array destructuring again here. 
  // the second parameter idx is a zero-based index we need to set the value of column sent 
  range.getDisplayValues().forEach(([name, date, sent], idx) => {
    // Use JavaScript strict comparison with === instead of ==
    // check if name and date match and sent is not already set to "sent"
    Logger.log(`Comparing name ${name} with ${clientName} and date ${date} with ${testDate}...`);
    if(name === clientName && date === testDate && sent !== "sent") {
      // we need to use idx + 2 because line 1 is the heading and idx of forEach() is zero-based but Spreadsheet API is one-based
      Logger.log(`Ìn row ${idx + 2} name and date match! Setting "sent"...`);
      sheet1.getRange(idx + 2, 3).setValue("sent");
    }
  })
  // set value "report sent" on sheet2  to "sent"
  sheet2.getRange("B3").setValue("sent")
  Logger.log("Report sent.")
}

Some pointers on your code.

  • You need to get actual values using getValue() or getDisplayValue() on a Range in order to obtain the values.
  • You can use destructuring which will be more convenient and more readable than dealing with indices all the time
  • You should use forEach or other JavaScript array methods to iterate over arrays
  • JavaScript has multiple comparison operators. You should use the strict equality operator === whenever possible as this also takes data types into account in contrast to the normal equality operator ==
  • You can use Logger.log() to print something to the console while the script is executing
  • Try to use const variables every time you are not planning to change the value of a variable instead of var. It is considered best practice to not change values at all but instead treat them as immutable as this avoids a lot of errors.

Inputs

Input in Sheet1

I have changed one row (in my copy of your Spreadsheet) so that one element actually fullfills the condition and is updated.

Sheet 1 input

Input in Sheet2

Sheet 2 input

Result

Output Sheet1

Sheet 1 output

Output Sheet2

Sheet 2 output

Related