I am attempting to copy some row values from Sheet1 to Sheet2 based on a criteria and a cell value where some rows are only copied when [Qualification] = 'Qualified' and, if [No of positions] = 1 then the values are copied once, if [No of positions] = 2 then the values are copied twice.

Below is an example.

Sheet1

Sheet2

I am currently running this query

function myFunction() {
  const srcSheetName = "Sheet1"; // Please set the source sheet name.
  const dstSheetName = "Sheet2"; // Please set the destination sheet name.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = [srcSheetName, dstSheetName].map(s => ss.getSheetByName(s));
  const [, ...v] = srcSheet.getDataRange().getValues();
  const values = v.flatMap(([, b, c, , e, f]) => e > 0 && f == "Qualified" ? [...Array(e)].fill([b, c, f]) : [])
dstSheet.getRange(dstSheet.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
} 

in the App Script and calling myFunction() in 'Sheet2' cell [A2] yet it returns this error:

Exception: You do not have permission to call setValues (line 515)

CodePudding user response：

Custom functions should preferably get their values through formula parameters. The values they return get placed in the formula cell, and down and to the right from there.

You can convert your function to a proper custom function like this:

/**
* Filters rows by matching the sixth column against criterion, and repeats
* each matching row as many times as specified in the fifth column.
*
* @param {Sheet1!A2:F} values The rows to filter and repeat.
* @param {"Qualified"} criterion The value to match to the sixth column in values.
* @return {Object[][]} The processed values.
* @customfunction
*/
function RepeatMatching(values, criterion = 'Qualified') {
  return values.flatMap(([, b, c, , e, f]) =>
    e > 0 && f == criterion ? [...Array(e)].fill([b, c, f]) : []
  );
}

To test the function, choose Insert > Sheet and put this formula in cell A2 of the new sheet:

=RepeatMatching(Sheet1!A1:F, "Qualified")