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 open to both an App Script solution or a formula. What I have managed to do so far is use the following query =query('Sheet1'!A:F, "select B,C,F where F = 'Qualified'", 0) on cell B2 in Sheet2. It only copies the values once and doesn't take into account the cell value.

P.S I am very new to using Google Sheets. Please assist.

CodePudding user response：

When Google Apps Script is used, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and save the script. When you use this script, please put a custom function like =SAMPLE(A2:F) to a cell. By this, the result is obtained.

const SAMPLE = v => v.flatMap(([, b, c, , e, f]) => e > 0 ? [...Array(e)].fill([b, c, f]) : []);

• When you change the retrieved values, please modify [, b, c, , e, f] and [b, c, f].

Testing:

When this script is run to your sample input values, the following result is obtained.

Note:

• If you want to retrieve the rows including "Qualified" in the column "F", you can also use the following sample script.

    const SAMPLE = v => v.flatMap(([, b, c, , e, f]) => e > 0 && f == "Qualified" ? [...Array(e)].fill([b, c, f]) : []);
  

  • In this case, the following result is obtained.

    

References:

• Custom Functions in Google Sheets
• flatMap()

Added:

About your following question,

How would you do this when copying from one sheet to another?

In this case, how about the following sample script?

Sample script:

Please set srcSheetName and dstSheetName and run the script.

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(1, 1, values.length, values[0].length).setValues(values);
  // Or, dstSheet.getRange(dstSheet.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
}