- You want to download a CSV file of
players_20.csv from https://www.kaggle.com/stefanoleone992/fifa-20-complete-player-dataset and put the CSV data to the Spreadsheet.
- You want to achieve this using Google Apps Script.
If my understanding is correct, how about this answer? Please think of this as just one of several answers.
Issue and workaround:
Unfortunately, the CSV data cannot be directly downloaded from the URL of https://www.kaggle.com/stefanoleone992/fifa-20-complete-player-dataset#players_20.csv. In order to download the CSV file, it is required to login to kaggle. As other pattern, you can also download it using API. In this answer, in order to download the CSV file, I used Kaggle's public API.
Usage:
1. Retrieve token file:
Before you use the script, please register an account to https://www.kaggle.com, and retrieve the token file. About how to retrieve the token file, you can see the official document.
In order to use the Kaggle’s public API, you must first authenticate using an API token. From the site header, click on your user profile picture, then on “My Account” from the dropdown menu. This will take you to your account settings at https://www.kaggle.com/account. Scroll down to the section of the page labelled API:
To create a new token, click on the “Create New API Token” button. This will download a fresh authentication token onto your machine.
In this script, the token object in the downloaded token file is used.
2. Run script:
Please copy and paste the following script to the container-bound script of Spreadsheet. And please set the variavles of csvFilename, path and tokenObject. In your case, I have already set csvFilename and path. So please set only your token object.
function myFunction() {
var csvFilename = "players_20.csv"; // Please set the CSV filename.
var path = "stefanoleone992/fifa-20-complete-player-dataset"; // Please set the path.
var tokenObject = {"username":"###","key":"###"}; // <--- Please set the token object.
var baseUrl = "https://www.kaggle.com/api/v1/datasets/download/";
var url = baseUrl + path;
var params = {headers: {Authorization: "Basic " + Utilities.base64Encode(tokenObject.username + ':' + tokenObject.key)}};
var blob = UrlFetchApp.fetch(url, params).getBlob();
var csvBlob = Utilities.unzip(blob).filter(function(b) {return b.getName() == csvFilename});
if (csvBlob.length == 1) {
var csvData = Utilities.parseCsv(csvBlob[0].getDataAsString());
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
} else {
throw new Error("CSV file of " + csvFilename + " was not found.");
}
}
The flow of this script is as follows.
- When the script is run, the kaggle command of
kaggle datasets download -d stefanoleone992/fifa-20-complete-player-dataset is run with Google Apps Script. By this, the ZIP file is downloaded.
- Retrieve the CSV file of
csvFilename from the downloaded ZIP file.
- Parse the CSV data from the CSV file.
- Put the CSV data to the active sheet.
- In this script, all data is processed with the blob. So the file is not created.
Note:
- It seems that the CSV data is large. So please wait until the script is finished.
- In my environment, I spent for about 150 seconds until the CSV data is put to the Spreadsheet.
- The CSV data of
players_20.csv has 18279 rows and 104 columns.
- If an error occurs at
Utilities.unzip(blob), please test to modify from var blob = UrlFetchApp.fetch(url, params).getBlob() to var blob = UrlFetchApp.fetch(url, params).getBlob().setContentTypeFromExtension().
References:
If I misunderstood your question and this was not the direction you want, I apologize.
Added 1:
If you want to select the columns you want to put, please modify above sample script as follows.
From:
var csvData = Utilities.parseCsv(csvBlob[0].getDataAsString());
var sheet = SpreadsheetApp.getActiveSheet();
To:
var csvData = Utilities.parseCsv(csvBlob[0].getDataAsString());
var needColumns = [1, 2, 3];
csvData = csvData.map(function(row) {return needColumns.map(function(col) {return row[col]})});
var sheet = SpreadsheetApp.getActiveSheet();
- In above modification, as the test case, the columns of 1, 2 and 3 are put to the Spreadsheet.
Added 2:
From the result of benchmark for putting CSV data to Spreadsheet, for example, how about using Sheets API for putting CSV data? For this, please modify above sample script as follows. Before you run the script, please enable Sheets API at Advanced Google services.
From:
var csvData = Utilities.parseCsv(csvBlob[0].getDataAsString());
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
To:
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var resource = {requests: [{pasteData: {data: csvBlob[0].getDataAsString(), coordinate: {sheetId: sheet.getSheetId()}, delimiter: ","}}]};
Sheets.Spreadsheets.batchUpdate(resource, ss.getId());
- In this case, I spent for about 50 seconds until the CSV data is put to the Spreadsheet.
Reference:
Utilities.parseCsv()? - TheMastercsvlink that actually consists of the data file? We should be able to help import that easily. - Sourabh Choraria