OnOpen or time trigger - move rows to another spreadsheet if 2 criteria met

Question

I have very little experience, in fact near none so some of my terminology is just recently 'herd' not known.

I have two Spreadsheets with 1 sheet named the same in each.

Spreadsheet "Production" with sheet "Master" - Sheet ID is 1goG0TS1_2jwlGRetREYNRVk-Q6TEy3iWG_5VXFoZlus

Spreadsheet "Archived_Production" "Master" - Sheet ID is 1Mr4LJmp1SmpDs1i8U_PE5uLOEVjLc599Vq87m9HwCx0

With an onOpen script, or maybe timed trigger, set in my "Production" spreadsheet I need to MOVE all rows to my "Archived_Production" spreadsheet that meet 2 criteria

Criteria 1 - the Date in Column B is 7 days older than the current date

BUT ONLY IF, also

Criteria 2 - the entry in Column O is "100%"

In the included screenshot example, given today's date is 11/19/14, only the first entry highlighted in yellow would get moved upon next opening of the spreadsheet.

Thank you, as always, in advance for any help.

Sorry, not enough postings yet to allow image upload, but hopefully text explains.

You should look at the onEdit trigger that will copy the data to another sheet as soon as the cell value is changed. — Amit Agarwal

rpm rpm · Accepted Answer · 2014-11-19T19:53:50

You can use this function to perform the operation as you want on onOpen() script.

function onOpen() {
  // I moved data from sheet1 to sheet2

  var production_sheet_id = 'Put your production sheet's id here';  // This is your source sheet
  var archived_sheet_id = 'Put your archived sheet's id here';   // This is your destination sheet
  var sheet1 = SpreadsheetApp.openById(production_sheet_id).getSheets()[0];
  var sheet2 = SpreadsheetApp.openById(archived_sheet_id).getSheets()[0];

  var rows = sheet1.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var i = 0; i < numRows; i++) {
    var oldDate = values[i][1];     // or var oldDate =  new Date('values[i][1]')
    var curDate = new Date();

    // Count time difference in milliseconds, convert them into days then.
    var diffInMilliSecs = (curDate.getTime() - oldDate.getTime());
    var diffInDays = diffInMilliSecs/1000/60/60/24;
    var diffInDays = Math.round(diffInDays);

    if(values[i][14] == 1.0 && Math.abs(diffInDays+30) >= 7)  // It evaluates 100% to 1.0
      sheet2.appendRow(values[i]);
  }
}

OnOpen or time trigger - move rows to another spreadsheet if 2 criteria met

1 Answers