Search code examples
google-sheetsgoogle-apps-script

How to copy a column based on value in two other columns to another column on the same sheet with Google Apps Script

I have a script here which Tanaike helped me to modify. It copies values in D6:D to H6:H if the values in the corresponding row in E6:6 does not equal "Out". Here is the original script:

function copyAllPresent() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName("Base");
  
  // Retrieve values from columns "D" and "E".
  const valuesToCopy = sheet.getRange(6, 4, sheet.getLastRow(), 2).getValues();

  // Create a new array.
  const values = valuesToCopy.map(([d, e]) => [e != "Out" ? d : null]);

  // Put the array to column "H".
  sheet.getRange(6, 8, values.length).setValues(values);
}

However, I would like to extend it further. If the value in C6:C is "", then I would also like to ignore the values in those cells. The final logic would be: Copy D6:D to H6:H if the corresponding rows in C6:C are not blank or if the value in the corresponding rows in E6:E is not "Out".

map does not seem to allow me to evaluate two separate columns and return the results. I also tried modifying the other script Tanaike offered as a solution, but it only appeared to be evaluating whether the value in corresponding rows in E6:E was "Out".

Here is a reference to the original question: How to copy a column if adjacent column does not contain a particular value to another column on the same sheet with Google Apps Script

The screenshot below shows the desired results:

Screenshot
Basketball Michael Jordan Michael Jordan
Baseball Babe Ruth Babe Ruth
Football Joe Montana Out
Wayne Gretzky
Cycling Lance Armstrong Lance Armstrong
Tennis Roger Federer Out
Football Lionel Messi Lionel Messi
Solution

  • Workaround:

    It is actually possible to evaluate two columns using a ternary operator inside a map function. To get your desired result, however, values from D6:D are copied to H6:H only if C6:C is not blank AND E6:E is not Out

    Try this slightly modified version of your existing script:

    function copyAllPresent() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName("Base");
  
  // Retrieve values from columns "C", "D", and "E".
  const valuesToCopy = sheet.getRange(6, 3, sheet.getLastRow(), 3).getValues();

  // Create a new array. 
  //Values are only pushed to the Array if "C" is not blank and "E" is not equal to "Out"
  const values = valuesToCopy.map(([c, d, e]) => [(e != "Out" && c != "") ? d : null]);

  // Put the array to column "H".
  sheet.getRange(6, 8, values.length).setValues(values);
}