CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Sharon Omwega
Sharon Omwega

Reputation: 123

Copy row to another sheet on Google Sheets based on criteria and cell value

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

Lead Type Lead ID Company Name Employment Type No of positions Qualification
External B21 KidRed Co. Full Time 1 Not Qualified
Africa B24 Freddie Co. Part Time 2 Qualified
Base B35 Akila Co. Full Time 1 Qualified
External B40 SeeQue Co. Part Time 1 Not Qualified

Sheet2

Lead ID Company Name Qualification
B24 Freddie Co. Qualified
B24 Freddie Co. Qualified
B35 Akila Co. Qualified

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.

Upvotes: 1

Views: 1300

Answers (1)

Tanaike
Tanaike

Reputation: 201378

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.

enter image description here

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.

      enter image description here

References:

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);
}

Upvotes: 1

Related Questions