I would like to fetch data from another google sheet, then retrieve all columns(been successful with this one). As for the rows, I would only like to retrieve all rows with these values, "Mrm1, Mrm2, Mrm3" from Measurement Column.

For example, I have these on another sheet,Original Data

And on another sheet, I want the result to be like this: Expected Retrieved Data

So far, I have this, but I'm not sure how to apply what I need on the rows. This script is retrieving everything.

function Country A() {
  
  var sss = SpreadsheetApp.openById("19-idD8PWuNxnvzRVqBlKhju2RkKxOe8nUQaf9ZMAcgk");  // Source spreadsheet - replace with actual key
  var ss = sss.getSheetByName('Compiled'); // Source sheet - change to actual name
  
  var numRows = ss.getLastRow() // get last row with data
  var numColumn = ss.getLastColumn(); // get last column with data

  var srange = ss.getRange(2,1,numRows,numColumn); // Source range - change to actual range
  var values = srange.getDisplayValues();
  
  
  var lss = SpreadsheetApp.getActiveSpreadsheet(); // Local spreadsheet - the one this script is in
  var targetnumRows = lss.getSheetByName('Country A').getMaxRows();
  var targetnumcolumns = lss.getSheetByName('Country A').getMaxColumns();
  
  var ls = lss.getSheetByName('Country A').getRange(2,1,numRows,numColumn); // Local sheet - change to actual name and Local range - change to actual range
  var TargetRange = lss.getSheetByName('Country A').getRange(2,1,targetnumRows,targetnumcolumns); // Local sheet - change to actual name and Local range - change to actual range
  
  TargetRange.clear() //Clear Sheet Content
  ls.setValues(values);  // Copy from source sheet to local sheet
  var sheet = lss.getSheetByName('Country A');
  var maxColumns = sheet.getMaxColumns();
  var lastColumn = sheet.getLastColumn();
  var maxRows = sheet.getMaxRows();
  var lastRow = sheet.getLastRow();
  
  if (maxColumns-lastColumn != 0){sheet.deleteColumns(lastColumn 1, maxColumns-lastColumn)};
  if (maxRows-lastRow != 0){sheet.deleteRows(lastRow 1, maxRows-lastRow)};
  var TargetHour = lss.getSheetByName('Country A').getRange(1,1).setValue(Utilities.formatDate(new Date(), "CST", "MM-dd-yyyy HH:mm:ss"));   
  
}

<iframe name="sif1" sandbox="allow-forms allow-modals allow-scripts" frameborder="0"></iframe>

Please help me. Thank you.

CodePudding user response：

The script stores the values in a 2D array. You have to iterate through the elements and delete the elements where column E (Col number 4) (A=0, B=1,...E=4) is not equal to "Mrm1, Mrm2, Mrm3". Then you paste the modified array in the second sheet.

Here is an example. You need to adapt it to your code.

function copyData() {
  var ss = SpreadsheetApp.openById("YOUR-ID");

  // Stores data as a 2D Array
  var data = ss.getSheetByName("Sheet1").getRange("A1:M10").getValues();

  // Remove rows where column E not equals "Mrm1, Mrm2, Mrm3"
  for (var i = 0; i < data.length; i  ) {
    if (data[i][4] !== "Mrm1" && data[i][4] !== "Mrm2" && data[i][4] !== "Mrm3") {
      data.splice(i,1);
      i--;
    }
  }

  // Paste data in sheet 2
  var pasteRange = "A1:M"   (data.length);
  ss.getSheetByName("Sheet2").getRange(pasteRange).setValues(data);
}