Pull data to the other sheet based on date

[Annika Chen]

Hi guys! I want to pull data from "OUTPUT" sheet to "INPUT" sheet based on a date range from start date to end date. Can anyone help take a look my code? It doesn't work! Appreciate it!

function Pull(){

  var app = SpreadsheetApp

  var ss_id = "1RAky4Jy1NoCJLMyeETK-8PnQv7pllnifHPUmxmlCVOE";

  var spreadsheet = app.openById(ss_id)

  var out_sheet = spreadsheet.getSheetByName('OUTPUT');

  var in_sheet = spreadsheet.getSheetByName('INPUT');

  var criteria_sheet = spreadsheet.getSheetByName('Criteria');

  var data = [];

  

  for(i=1; i<out_sheet.length; i++){

    var out_sheet_date = out_sheet[i][16]; //column 16 is where the dates are

    var startDate = criteria_sheet[1][0]; 

    var endDate = criteria_sheet[1][1];

    

    if(out_sheet_date > startDate && out_sheet_date < endDate){ //pull data by date

      

      data = data.push(out_sheet[i])}

  }

    

  in_sheet.getrange(1,1,data.length,data[0].length).setValues(data);

}

  

[CBM Services]

Annika,

You are using the Spreadsheets as if they are arrays and they are not. You need to use the methods to get the data and set the data in the spreadsheet.

Loop up the methods that you need to use such as getValues() and setValues().

Also look up the range variable you need to use to allow you to index into the spreadsheet.

I am on my phone so can't give you a handy link, but google search works well. :-)

---

From: Annika Chen
Sent: ‎2020-‎07-‎14 8:03 AM
To: Google Apps Script Community
Subject: [Apps-Script] Pull data to the other sheet based on date

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/209e4c12-05af-4e80-9522-ebdecd711cbfo%40googlegroups.com.

[Alan Wells]

Note changes.  I didn't make all the changes necessary.

You need to get the data.

function Pull(){

  var app = SpreadsheetApp

  var ss_id = "1RAky4Jy1NoCJLMyeETK-8PnQv7pllnifHPUmxmlCVOE";

  var spreadsheet = app.openById(ss_id)

  var out_sheet = spreadsheet.getSheetByName('OUTPUT');

  var in_sheet = spreadsheet.getSheetByName('INPUT');

  var criteria_sheet = spreadsheet.getSheetByName('Criteria');

  var dataOut_sheet = out_sheet.getDataRange().getValues();//Get data in out sheet

  var data = [];

  

  for(i=1; i<out_sheet.length; i++){

    var out_sheet_date =  dataOut_sheet[i][16]; //column 16 is where the dates are

    Logger.log(' out_sheet_date : ' + out_sheet_date )

[Annika Chen]

function Pull(){
  var app = SpreadsheetApp;
  var ss_id = "1RAky4Jy1NoCJLMyeETK-8PnQv7pllnifHPUmxmlCVOE";
  var spreadsheet = app.openById(ss_id);
  var out_sheet = spreadsheet.getSheetByName('OUTPUT');
  var in_sheet = spreadsheet.getSheetByName('INPUT');

  var out_values = out_sheet.getRange(1,1,out_sheet.getLastRow(),out_sheet.getLastColumn()).getValues();

  var criteria_sheet = spreadsheet.getSheetByName('Criteria');

  var cri_values = criteria_sheet.getRange(1,1,2,2).getValues();
  var data = [];
 
  for(i=1; i<out_values.length; i++){
    var out_sheet_date = out_values[i][16]; //column 16 is where the dates are
    var startDate = cri_values[1][0];
    var endDate = cri_values[1][1];

   
    if(out_sheet_date > startDate && out_sheet_date < endDate){ //pull data by date

     
      data.push(out_sheet.getRange(i, 1,i,out_sheet.getLastColumn()))}
  }
   
  in_sheet.getrange(1,1,data.getLastRow(),data.getLastColumn()).setValues(data);


 
}
}

Thank you very much! I solved the get range and get values problem, but still doesn't work. Can you please also take a look at the date comparison and data.push? 

[cbmserv...@gmail.com]

Annika,

 

I see 2 problems still in the code:

 

1. You are treating data variable (which is an array) as a spreadsheet. You can not use getLastColumn and getLastRow on it.  So statements like data.getLastColumn or row need to change.
2. Last statement is writing data back to insheet? Is that correct, I thought output was meant to go to outsheet?

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/38e1b990-60ed-45bf-8e6d-95fa09ad1b20o%40googlegroups.com.

[Annika Chen]

function Pull(){

  var app = SpreadsheetApp;

  var ss_id = "1RAky4Jy1NoCJLMyeETK-8PnQv7pllnifHPUmxmlCVOE";

  var spreadsheet = app.openById(ss_id);

  var out_sheet = spreadsheet.getSheetByName('OUTPUT');

  var in_sheet = spreadsheet.getSheetByName('INPUT');

  var out_values = out_sheet.getRange(1,1,out_sheet.getLastRow(),out_sheet.getLastColumn()).getValues();

  var criteria_sheet = spreadsheet.getSheetByName('Criteria');

  var cri_values = criteria_sheet.getRange(1,1,2,2).getValues();

  var data = [];

  

  for(i=1; i<out_values.length; i++){

    var out_sheet_date = out_values[i][16]; //column 16 is where the dates are

    var startDate = cri_values[1][0]; 

    var endDate = cri_values[1][1];

    

    if(out_sheet_date > startDate && out_sheet_date < endDate){ //pull data by date

      

      data.push(out_sheet.getRange(i, 1,i,out_sheet.getLastColumn()))}

  }

    

  in_sheet.getrange(1,1,data.length,data[0].length()).setValues(data);

  

}

Hi! About the problems you pointed out:

1. I changed the data.getlastcolumn to data.length and data[0].length, and still show up "TypeError: Cannot read property 'length' of undefined (line 22, file "Code")"

2. Yes, may script maybe confusing. So output is where data get pull out from, and input is where I want to put data in.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

[cbmserv...@gmail.com]

Annika,

 

Two items again:

 

1. Length is not a function. So remove the parenthesis. Usage of the length method is with no parenthesis.
2. Remember that when you import data from a spreadsheet into an array, the numbers are all off by 1. Spreadsheet first row is 1. An array first item is 0. Same for columns.. So if you specify column 16 below in your script, the data should be in column 17 of the spreadsheet. Make sure you make allowances for this when you read/write from spreadsheet also in terms of how you specify row/column numbers.

 

Thanks

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/38e1b990-60ed-45bf-8e6d-95fa09ad1b20o%40googlegroups.com.

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/f8d8d110-3168-4971-a402-727deb83ef87o%40googlegroups.com.