CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Digital Farmer
Digital Farmer

Reputation: 2107

Filter data from current and future date only

I am importing data from a .CSV file

function myFunction() {
  var url = "https://projects.fivethirtyeight.com/soccer-api/club/spi_matches.csv";
  var data = UrlFetchApp.fetch(url).getContentText();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var resource = {requests: [{pasteData: {
    data: data,
    coordinate: {sheetId: sheet.getSheetId()},
    delimiter: ","
  }}]};
  Sheets.Spreadsheets.batchUpdate(resource, ss.getId());
}

The data from past dates does not interest me and I would like to filter to make the sheet smaller and also to make my work easier, via script I could not filter directly from the data source and when I try to filter by a formula in another page, the filter does not work.

=FILTER('Page1'!A2:A,'Page1'!A2:A>=TEXT(now(),"dd/mm/yyyy"))

I would like to know what options I have to be able to filter this data, because I will update it daily, and this filter is very important for the study I am going to do.

If there is any way to filter already at import time, it would be even better, but if i need to use functions on another spreadsheet page, no problem either.

Upvotes: 0

Views: 640

Answers (1)

Tanaike
Tanaike

Reputation: 201358

  • You want to retrieve CSV data and put it to Spreadsheet.
  • When the CSV data is put, you want to remove the data of before today.
    • The date values are the column "A".
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

In this modification, the following flow is used.

  1. Retrieve CSV data with UrlFetchApp.fetch().
  2. Parse CSV data with Utilities.parseCsv().
  3. Remove the values before today from the parsed data with filter().
  4. Put the values to Spreadsheet with setValues().

Modified script:

function myFunction() {
  var url = "https://projects.fivethirtyeight.com/soccer-api/club/spi_matches.csv";
  var data = UrlFetchApp.fetch(url).getContentText();
  var csv = Utilities.parseCsv(data);
  var d = new Date();
  d.setHours(0, 0, 0, 0);
  var today = d.getTime();
  var values = csv.filter(function(e, i) {
    var temp = new Date(e[0]);
    temp.setHours(0, 0, 0, 0);
    return i > 0 && temp.getTime() >= today;
  });
  values.unshift(csv[0]);
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
  • In this script, the values are put to the active sheet.

References:

If I misunderstood your question and this was not the result you want, I apologize.

Upvotes: 1

Related Questions