Scripts, scripts, scripts!

Ask for Help
92

Hello good sir,

I found myself in a similar situation and with the help of @Darren_Murphy we came up with this script. It will copy the values only from a source sheet to a target sheet while skipping the ones you don’t want to copy yet. Hopefully this could point you in the right direction.

With a little tweaking it could delete the values from the source sheet that have already been processed. Then it would satisfy both your needs in one execution.

Summary 
function copy_new_rows_to_database() {
  console.time('Archive');
  // Open the source sheet and read all data into an array
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourcesheet = ss.getSheetByName('YOURSOURCESHEET');
  var data = sourcesheet.getDataRange().getValues();
  data.shift(); // Discard the header row
  var data_rows = data.length;
  if (data[0][2] == '') {  // If there is no data in the second row, assume the sheet is empty
    console.log('No Data in source sheet')
    console.timeEnd('Archive');
    return;
  }
  var archive_data = [];
  var moved_rows = [];

  // Iterate through the array of data, adding any matching rows to the archive_data array
  // and tag the original row as 'Moved'
  var row = 0;
  while (row <= data_rows) {
    var rec = data[row];
    if (typeof rec == 'undefined') { // Exit as soon as we hit the first empty row
      console.log("Done at row %s", row+1);
      break;
      }
    if (rec[5] != 'ACTIVE' && rec[16] == '') { // Set condtions to continue
      archive_data.push(rec.slice(0,16));
      moved_rows.push(['Moved']);
    }
    else {
      moved_rows.push([rec[16]]);
    }
    row++;
  }
  
  var source_sheet_rows_processed = row;
  var archive_rows = archive_data.length;
  // If we found any rows that need moving, append them to the end of the Database sheet
  // And then write the altered data array (with newly archived rows tagged), back to the source sheet
  if (archive_rows) {
    var archive_sheet = ss.getSheetByName('YOURTARGETSHEET');
    var start_row = archive_sheet.getLastRow()+1;
    archive_sheet.getRange(start_row, 1, archive_rows, 16).setValues(archive_data);
    console.log("Archived %s rows", archive_rows);
    sourcesheet.getRange(2,17,source_sheet_rows_processed, 1).setValues(moved_rows); // Start from row 2 so we don't mess with the header
  }
  else {
    console.log('No new rows to archive.');
  }
  console.timeEnd('Archive')
}

Note: If some of the data/conditions you need to execute the script live in the Glide Data Editor only, you would need to find a way to bring that logic into GSheets to use with scripts.

4 Likes