vendredi 1 mars 2019

Google Script :Push only rows that meet criteria to Array

I have a spreadsheet of data and I need to pull rows of data from source to another spreadsheet that meet certain criteria. I made a crude loop to do this but since it references the google app multiple times it takes much too long to run and times out.

I know I can save these values to an array and pull what I need to an additional array but I haven't successfully figure out how to do this to where there are no spaces.


function overallGrades() 
{

  var source = SpreadsheetApp.openById('SourceID').getSheetByName("Sheet1")
  var values = source.getDataRange().getValues();
  var lastrow = source.getLastRow();
  var lastcolumn = source.getLastColumn();

  var Dest = SpreadsheetApp.openById('destinationID').getSheetByName("Sheet1");
  var DestLastRow = Dest.getLastRow();

  for (var i = 0; i < values.length; i++)
  {
    var checkValue = values[i][13];
    var course = values[i][6];

    if (checkValue =="criteria")
    {
      var DestLastRow = Dest.getLastRow();
      var copyRange = source.getRange(i+2,1,1,lastcolumn).getValues(); 
      Dest.getRange(DestiLastRow+1,1,1,lastcolumn).setValues(copyRange);

      var dRange = Dest.getRange(DestLastRow+1,lastcolumn+1,1,1);
      var date = Utilities.formatDate(new Date(), "GMT - 8", "yyyy-MM-dd");
      dRange.setValue(date);


      }
    }
  }

Aucun commentaire:

Enregistrer un commentaire