I am trying to write a Google Apps Script function which scans every row of my spreadsheet, and if column 36 has a specific string, will look at column 31, column 23, etc. of that row to create an array with the data in these columns.

I think it will ultimately require an array updating function inside an if statement inside a loop.

1. Loop through every row of column 36
2. If string matches target string
3. Add row's data to an array
4. Update another sheet with data from this array

Thank you!

Here is what I've tried so far:

`

    function myFunction() {
    var sheet = SpreadsheetApp.getActive().getSheetByName("Database");
    var range = sheet.getRange(2, 36).getValue();
    Logger.log(range);
    }

`

CodePudding user response：

Here is the code, replace the string of Target Sheet Name, the specific String and etc on your own.

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // DataBase sheet:
  const dbSheet = ss.getSheetByName("Database");
  // Target sheet for placing your outputs:
  const tgSheet = ss.getSheetByName("Target Sheet Name");
  // Get all values as a 2D array from DataBase sheet:
  const sourceValues = dbSheet.getDataRange().getValues();
  // Declare result array.
  const results = [];
  // Loop through every row:
  for (const row of sourceValues) {
    const specificString = 'Your String';
    // check the value of column 36 of each row.
    const check = row[36] === specificString;
    // if condition check returns true, form an array with column 31, column 23 and 'etc' of this row, than push it into the result array.
    if(check) results.push([row[31],row[23],'etc']);
  }
  // print the result onto your Target Sheet, start from A1.
  tgSheet.getRange(1,1,results.length,results[0].length).setValues(results);
}

CodePudding user response：

Move selected rows to another sheet

function elfunko(specificvalue) {
  const ss = SpreadsheetApp.getActive();
  const vs = ss.getSheetByName("Database").getDataRange().getValues().filter(r => r[35] == specificvalue);
  const sh = ss.getSheetByName("Sheet1");
  sh.getRange(sh.getLastRow()   1, 1, vs.length, vs[0].length).setValues(vs);
}