I have a table with data in columns A:Ak. Some of the data contains formulas and when a row of data is complete (i.e. the status in column W is "Y") I would like to copy that row in place and only past the values.

This is what I have tried:

function Optimize() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues(); 
  for (var i = 0; i <= numRows - 1; i  ) {
    var row = values[i];
    // This searches all cells in columns W copies and pastes values in row if cell has value 'Y'
    if (row[22] == 'Y') {
    sheet.getDataRange(sheet.getCurrentCell().getRow() - 0, 1, 1, sheet.getMaxColumns()).activate();
    sheet.getActiveRange().copyTo(sheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    rowsCopiedpasted  ;
    }
  }
}

When I am in the sheet and click on the cell in column A of a row with a "Y" value this works, but I need it to go through the whole sheet and copy/paste values of all rows with "Y" in column W.

CodePudding user response：

Try this:

function Optimize() {
  const sh = SpreadsheetApp.getActiveSheet();
  const rg = sh.getDataRange();
  const vs = rg.getValues(); 
  const lc = sh.getLastColumn();
  let o = vs.forEach((r,i) => {
    if(r[22] == "Y") {
      sh.getRange(i   1, lc   1,1,lc).setValues([r])
    }
  });
}