0

I need to copy/import lines from all sheets into one different sheet based on multiple conditions.

In other words, i need to automatically copy/import, from all existing sheets on spreadsheet, the lines where column B is "fruit" and column C is "red".

I can import the lines from Sheet1 like this:

=FILTER(
IMPORTRANGE("1HXbrA_Ks8yuIit6UOBdZEdan3hSRGnnuGvrIueDCbXM";"Sheet1!A2:C") ; 
           (Sheet1!B2:B = "fruit");(Sheet1!C2:C = "red")
)

But i need to import the correponding lines from all the existing sheets.

Maybe using a script is the right way to achieve this, but i am not able to do it.

Here is a live spreadsheet for testing:

https://docs.google.com/spreadsheets/d/1HXbrA_Ks8yuIit6UOBdZEdan3hSRGnnuGvrIueDCbXM/edit?usp=sharing

This test spreadsheet have only 2 sheets, but the real spreadsheet have several sheets, so please take that into consideration.

Thank you very much.

Lucas Matos
  • 1,112
  • 5
  • 25
  • 42
  • Show your current script with [mcve] – TheMaster Sep 01 '20 at 02:44
  • Sorry but i do not have a script. I tried with filter direct into cell, but was not able to import from multiple sheets. – Lucas Matos Sep 01 '20 at 02:46
  • I proposed a sample script as an answer. Could you please confirm it? If that was not the direction you expect, I apologize. And also, when I saw your shared Spreadsheet, I found that the script is included. Can you add it to your question? – Tanaike Sep 01 '20 at 03:19
  • Duplicate of https://stackoverflow.com/questions/36511157 https://stackoverflow.com/questions/62958491 – TheMaster Sep 01 '20 at 03:21

2 Answers2

2

I believe your goal as follows.

  • You want to retrieve the rows which have fruit and red in the columns "B" and "C", respectively.
  • You want to put the retrieved rows to the destination sheet.
  • You want to achieve above using Google Apps Script.

Flow:

The flow of this sample script is as follows.

  1. Retrieve all sheets.
  2. From each sheet, retrieve the rows which have fruit and red in the columns "B" and "C", respectively.
    • At this time, destinationSheetName sheet is excluded.
  3. Put the retrieved rows to the destination sheet.

Sample script:

Before you use this script, please set destinationSheetName. And in this sample script, destinationSheetName and "MERGED RED FRUITS" sheet in your shared Spreadsheet are excluded for retrieving rows.

function myFunction() {
  const destinationSheetName = "MERGED RED FRUITS (DESIRED RESULT)";
  const excludeSheetNames = [destinationSheetName, "MERGED RED FRUITS"];
  
  // 1. Retrieve all sheets.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  
  // 2. From each sheet, retrieve the rows which have `fruit` and `red` in the columns "B" and "C", respectively.
  const values = sheets.reduce((ar, s, i) => {
    if (!excludeSheetNames.includes(s.getSheetName())) {
      const [,...v] = s.getDataRange().getValues();
      const temp = v.filter(([,b,c]) => b == "fruit" && c == "red");
      if (temp.length > 0) ar = ar.concat(temp);
    }
    return ar;
  }, []);
  
  // 3. Put the retrieved rows to the destination sheet.
  const dest = ss.getSheetByName(destinationSheetName);
  dest.getRange(2, 1, values.length, values[0].length).setValues(values);
}

Note:

  • In this sample script, the duplication of the values is not checked. Please be careful this.
  • When I saw your current script in your shared Spreadsheet, it seems that getValue() is used in the loop. In this case, the process cost will be high. So I proposed above flow.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Nice! That seems to work properly. Looks good on test spreadsheet. I will try on my real spreadsheet and give a feedback! Thank you again Tanaike. – Lucas Matos Sep 01 '20 at 03:34
  • 1
    It worked perfectly on my real and larger spreadsheet. I just had to make some adjustments to fit the real dimensions of my real sheets. Thank you my friend. – Lucas Matos Sep 01 '20 at 04:11
1

Here is a script that will do what I think you want:

function myFunction() {
  var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1HXbrA_Ks8yuIit6UOBdZEdan3hSRGnnuGvrIueDCbXM/edit'); 
  var sheet = ss.getSheets()[0];
  let currentRow = 2;
  let rows = []
  while(sheet.getRange("B" + currentRow).getValue() != "") {
    if(sheet.getRange("B" + currentRow).getValue() == "fruit" && sheet.getRange("C" + currentRow).getValue() == "red") {
      rows.push(currentRow)
      console.log("Match " + currentRow);
    }
    currentRow++;
  }
  Logger.log(rows)
  let destSheet = ss.getSheetByName('TEST');
  let deststartRow=5;
  for(var row of rows) {
    sheet.getRange("A"+row +":C"+row).copyTo(destSheet.getRange("A"+deststartRow +":C"+deststartRow))
    deststartRow++;
  }
 
}
memerson
  • 36
  • 1