Move filtered array to another sheet using Google Apps Script

Question

I have a sheet with 6 columns that is being updated with a webhook using the doPost function below that works just fine.

I have filtered only the rows where column 5 = 'true' (index [4]), but I would like to move all columns that match this condition of true in column 5 into another sheet and then clear the first sheet leaving only the header rows in the first sheet.

I'm nearly there, but I would appreciate it if anyone has any suggestions to achieve this final step. Thanks for reading...

function doPost(e) {

  var jsonString = e.postData.getDataAsString();
  var event = JSON.parse(jsonString)
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Import");
  var timeStamp = new Date();
  var time = Utilities.formatDate(timeStamp, "GMT+1", "dd/MM/yyyy, h:mm a");
  var nextRow = sheet.getLastRow()+1;

  //Insert the data into the sheet  
  sheet.getRange(nextRow, 1).setValue(time);
  sheet.getRange(nextRow, 3).setValue(jsonString);
  sheet.getRange(nextRow, 4).setValue(event.data.amount);
  sheet.getRange(nextRow, 5).setValue(event.data.metadata.is_supplementary);
  sheet.getRange(nextRow, 6).setValue(event.data.reference);
}

function getData() {
  var rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  var filteredRows = rows.filter(function(row){
    var is_supplementary = row[4];
    if (is_supplementary == true){
      return row;
    }
  })
  Logger.log(filteredRows);
}

user30hu · Answer

Try this:

function doPost(e) {
  const jsonString = e.postData.getDataAsString();
  const event = JSON.parse(jsonString)
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName("Import");
  const ts = new Date();
  const time = Utilities.formatDate(ts, "GMT+1", "dd/MM/yyyy, h:mm a");
  const nextRow = sh.getLastRow() + 1;
  sh.getRange(nextRow, 1).setValue(time);
  sh.getRange(nextRow, 3, 1, 4).setValues([[jsonString, event.data.amount, event.data.is_supplementary, event.data.reference]]);//doing last 3 all at once
}

function getData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();//getting active sheet
  const vs = sh.getRange(2,1,sh.getLastRow() - 1,sh.getLastColumn()).getValues().filter(r => r[4]);//test truthiness
  const osh = ss.getSheetByName("output");
  osh.getRange(osh.getLastRow() + 1, 1, vs.length,vs[0].length).setValues(vs);
  sh.getRange(2,1,sh.getLastRow() - 1, sh.getLastColumn()).clearContent();
}

Move filtered array to another sheet using Google Apps Script

1 Answers