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

Reputation: 2107

Import data from current and future date only

I am trying to import fivethirtyeight data, but when trying to import the full link from the CSV file, it exceeds the limit allowed by google.

So ... I would like to know how I could filter so that only today's games and future dates are imported. From the games of past dates there is no value to me.

=IMPORTDATA("https://projects.fivethirtyeight.com/soccer-api/club/spi_matches.csv")

IMPORTDATA didn't work because it exceeds the spreadsheet limit, because it saves past game data since 2016, so the data list gets too big.

I just need the current date and future date games

Upvotes: 0

Views: 147

Answers (1)

Tanaike
Tanaike

Reputation: 201358

How about this answer?

It seems that the size of CSV data is 4,183,375 bytes, and 32,101 rows and 22 columns. So how about putting the data using Google Apps Script? Unfortunately, this CSV cannot be put using the custom function, because of the large size.

From the benchmark for importing CSV data to Spreadsheet, when Sheets API is used, the process cost can be reduced from the method with Utilities.parseCsv() and setValues(). So in this answer, Sheets API is used for putting CSV values to Spreadsheet. Please think of this as just one of several answers.

Sample script 1:

Before you use the following scripts, please enable Sheets API at Advanced Google services.

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());
}
  • In this script, the CSV data is put from the cell "A1" of the active sheet.

Sample script 2:

If you cannot use Sheets API, you can also use the following 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 sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, csv.length, csv[0].length).setValues(csv);
}

References:

If this was not the direction you want, I apologize.

Upvotes: 1

Related Questions