Search code examples
google-sheetsgoogle-apps-script

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

I would like to copy values from D6:D to H6:H if the corresponding row in E6:E does not contain the value "Out".

Here is a screenshot of my desired results: Screenshot
Michael Jordan Michael Jordan
Babe Ruth Babe Ruth
Joe Montana Out
Wayne Gretzky Wayne Gretzky
Lance Armstrong Lance Armstrong
Roger Federer Out
Lionel Messi Lionel Messi

Here is the script that I have tried:

function copyAllPresent() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName("Base");
  const valuesToCopy = sheet.getRange(6,4,sheet.getLastRow(),1).getValues();
  const rangeToPaste = sheet.getRange(6,8,sheet.getLastRow(),1);
  valuesToCopy.forEach(r => {
    if(r[4] != "Out") {
      rangeToPaste.setValues(valuesToCopy);
    }
  })
}

This just copies the entire range(D6:D) to H6:H regardless of the value in E6:E.

Solution

  • In your current script, at rangeToPaste.setValues(valuesToCopy);, the values valuesToCopy are put into column "H". By this, all values are put into column "H". When your script is modified, as a simple modification, how about the following modification?

    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);
}

    As an additional modification, from but did not know how to created the new array., when const values = valuesToCopy.map(([d, e]) => [e != "Out" ? d : null]); is created by a for loop, it becomes as follows. I thought that this might help understanding to create a new array. In this modification, the same result with the above script is obtained.

    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 = [];
  for (let i = 0; i < valuesToCopy.length; i++) {
    if (valuesToCopy[i][1] != "Out") {
      values.push([valuesToCopy[i][0]]);
    } else {
      values.push([null]);
    }
  }

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