Moving rows to another sheet based on certain criteria in google apps script-CodePudding

I am working on creating a script that needs to push rows from one sheet of a google workbook to another based on how the row is categorized by an entry in another column. This needs to be also adaptable to have it push to a different google workbook in some cases in the future. I have tried multiple iterations of the following script and it will pull the rows over and then updated background colors, but it is just iterating through all the data and pulling over everything instead of just those rows with an "X" in the relevant column.

What I'd like it to do is pull only those on the "Feedback" tab which are not set to green as the cell color in column F, and those with an "X" in column F, then to have it set the cell to green once it has pulled so that it won't pull the same information next time I run the script. I want to then have it do the same thing using column G.

Here is a test doc I have been testing with. https://docs.google.com/spreadsheets/d/1JLyEuVijQ8MvfOKrtbRD_YKmRDnTCxf7qCSw9Ggty_Y/edit#gid=384324173

This is the code I have currently:

function oneFeedback() {
  var sss = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sss.getSheetByName("Feedback");
  var s = ss.getSheetName();
  var data = ss.getDataRange().getValues();

  var bostab = sss.getSheetByName("1"); 

  if(s !== "Feedback"){
    Browser.msgBox("This option isn't available for this sheet.")
  }
  else
  {
 
  for(var i = 2; i < data.length; i  ){
      if(ss.getRange(i 1,6).getBackground !== "#d9ead3"){
      if(ss.getRange(i 1,6) !== ""){
        var values = ss.getRange(i 1,1,1,5).getValues();
              
          bostab.insertRowBefore(2);
          bostab.getRange(2,2,1,5).setValues(values).setFontColor("#000000");

          ss.getRange(i 1,6).setBackground("#d9ead3");     
                  
      }
      }
      Browser.msgBox("Complete")
  }
  }
}

The script is set to run from selecting a menu item in the "Extras" menu that is being created using the "Code.gs" script on this doc.

CodePudding user response：

Modification points:

In your script, getBackground of ss.getRange(i 1,6).getBackground might be getBackground().
When getValues() and setValues() are used in a loop, the process cost will become high. Ref (Author: me)
Only column "F" is used.

When these points are reflected in your script, how about the following modification?

Modified script:

function oneFeedback() {
  // Ref: https://stackoverflow.com/a/53678158
  const columnIndexToLetter_ = index => (a = Math.floor(index / 26)) >= 0 ? columnIndexToLetter_(a - 1)   String.fromCharCode(65   (index % 26)) : "";

  // Retrieve source sheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName("Feedback");
  if (!srcSheet) Browser.msgBox("This option isn't available for this sheet.");

  // Retrieve source values.
  const range = srcSheet.getDataRange();
  const [header, ...values] = range.getValues();
  const [, ...backgrounds] = range.getBackgrounds();

  // Create an object for putting to destination sheets.
  const offset = 5; // This is from your question.
  const dstSheets = header.splice(offset);
  const obj = dstSheets.reduce((o, e) => (o[e] = [], o), {});
  const res = values.reduce((o, r, i) => {
    dstSheets.forEach((h, j) => {
      const idx = offset   j;
      if (r[idx] == "X" && backgrounds[i][idx] != "#d9ead3") {
        o[h].push(r);
        o.ranges.push(`${columnIndexToLetter_(idx)}${i   2}`);
      }
    });
    return o;
  }, { ...obj, ranges: [] });

  // Put values to destination sheets.
  dstSheets.forEach(e => {
    const v = res[e];
    if (v.length > 0) {
      const dstSheet = ss.getSheetByName(e);
      dstSheet.getRange(dstSheet.getLastRow()   1, 1, v.length, v[0].length).setValues(v);
    }
  });

  // Set background colors of source cells.
  if (res.ranges.length == 0) return;
  srcSheet.getRangeList(res.ranges).setBackground("#d9ead3");
}

When this script is run, I thought that your goal might be able to be achieved.

Modification points:

Modified script:

References: