I have a master sheet that contains rows of information and what I want to do is export this information into a new sheet if the row is not highlighted yellow

I have been using this, however cannot figure out how to implement the 2d array aspect to copy all information over.

function log(ss, range, values){
  var newRange;
  var rule = range.getDataValidations();
  var j = 0;
  var x = 0;
  for (i = 0; i < rule.length; i  ){
    if ((ss.getSheetByName('Cover').getRange("A1:N1000").getBackground() != "#ffff00") && (values[i][0])){
      for (x; x < 14; x  ){
        newRange[x] = newRange[i];
          newRange[j][x] = values[i][x];
          j  ;
          //Logger.log(values[i][x]);
        }
      }
    }
    //if (values[i][0]) Logger.log(values[i][0]);
  }

CodePudding user response：

You can get the background without the rules. Best practices is to batch process thinks. So:

1. Get all the backgrounds and all the values in one call.
2. Use the .getLastRow() instead of (possibly) getting to many empty rows with the hardcoded A1:N1000

function log(){
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = ss.getSheetByName("Cover")
  //Flatten the 2D array so it is 1D. Easier for the .forEach
  const backgrounds = sheet.getRange(1,1,sheet.getLastRow()).getBackgrounds().flat()
  // Use dynamic ranges with .getRange(start_row, start_col, num_of_rows, num_of_cols)
  const values = sheet.getRange(1,1,sheet.getLastRow(), 14).getValues()
  
  //Empty array to push the values to.
  const toKeep = []

  //The bg holds the current value inside the array and the i is the index, we use that to match the same row from the values array.
  backgrounds.forEach((bg, i) => {
    if(bg !== "#ffff00"){
      toKeep.push(values[i])
    }
  })

  //Do something with the values, as example:
  const log = ss.getSheetByName('Log')
  log.getRange(log.getLastRow()   1, 1, toKeep.length, toKeep[0].length).setValues(toKeep)


}

CodePudding user response：

Copy rows not highlighted to another Spreadsheet and append to the destination

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const ssh = ss.getSheetByName("Cover");
  const sbg = ssh.getDataRange().getBackgrounds();
  const svs = ssh.getDataRange().getValues().filter((r, i) => {
    if (sbg[i].every(c => c != "#ffff00")) {
      return r;
    }
  }).filter(e => e);
  const dss = SpreadsheetApp.openById("dest id");
  const sh = dss.getSheets()[0];//default active sheet you can chose what you want
  sh.getRange(sh.getLastRow()   1, 1, svs.length, svs[0].length).setValues(svs);
}