Trying to check multiple cells with .isBlank()

user_19182798 0
Viewed 47

I'm currently working on an Google Apps Script that will basically reset my spreadsheet at the end of the day.

if(ss.getRange('D2').isBlank()){ 
    ss.getRange('C2').moveTo(ss.getRange('D2')); 
  }
  if(ss.getRange('D3').isBlank()){
    ss.getRange('C3').moveTo(ss.getRange('D3'));
  }
  if(ss.getRange('D4').isBlank()){
    ss.getRange('C4').moveTo(ss.getRange('D4'));
  }
  if(ss.getRange('D5').isBlank()){
    ss.getRange('C5').moveTo(ss.getRange('D5'));
  }

That is what I am currently using but as you can probably tell this get's messy fast. Is there a better way to do this?

google-apps-script google-sheets javascript
2 Answers

It is way more efficient to work with arrays instead of individual values. In this way you only have to get and set the data once.

In the following script adjust the sheet name and desired range. Range C2:D10 assumes that column D is part of the if statement and C is the column you want to get the values if D is empty.

function myFunction() {

  const sheetName = 'Sheet1'; // define the name of the sheet
  const range = 'C2:D10'; // define the range

  const sss = SpreadsheetApp.getActive();
  const ss = sss.getSheetByName(sheetName);
  const data = ss.getRange(range).getValues();
  
  data.forEach((r,i)=>{
    data[i][1] = r[1] ==''? r[0] : r[1];
  })

  ss.getRange(range).setValues(data);

}
user_112252910

Try it with a forEach

function lfunko() {
  const ss = SpreadsheetApp.getActive();
  ['D2', 'D3', 'D4', 'D5'].forEach(r => {
    let rg = ss.getRange(r);
    if (rg.isBlank()) {
      ss.getRange(r).offset(0, -1).moveTo(rg)
    }
  });
}

While it is true there are certainly more efficient ways of doing this. I felt that the ui was improved because all you have to do is tack a few more ranges on to the array and they need not be contiguous. Of course if difference range and difference methodologies then you would have to modify the script.

user_72150910
Related