Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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);
}
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading