Reputation: 2107
When trying to use the IMPORTDATA
function for this file:
https://www.kaggle.com/stefanoleone992/fifa-20-complete-player-dataset#players_20.csv
An unexpected error occurs that says it is impossible to import data into the spreadsheet. Is there any other way that I can bring this data to my spreadsheet?
This data would be very important to the work I'm doing. It would save me from almost 3 months of work to be able to type and copy everything and then filtering according to my need.
It would be very important to be able to import at least the simple info of all players, but do not necessarily have to import all columns of info from each player. The amount of columns can import is already perfect.
I would be grateful if there was any way.
Upvotes: 1
Views: 2321
Reputation: 201378
players_20.csv
from https://www.kaggle.com/stefanoleone992/fifa-20-complete-player-dataset
and put the CSV data to the Spreadsheet.If my understanding is correct, how about this answer? Please think of this as just one of several answers.
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.
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.
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.");
}
}
Flow:
The flow of this script is as follows.
kaggle datasets download -d stefanoleone992/fifa-20-complete-player-dataset
is run with Google Apps Script. By this, the ZIP file is downloaded.csvFilename
from the downloaded ZIP file.players_20.csv
has 18279 rows and 104 columns.Utilities.unzip(blob)
, please test to modify from var blob = UrlFetchApp.fetch(url, params).getBlob()
to var blob = UrlFetchApp.fetch(url, params).getBlob().setContentTypeFromExtension()
.If I misunderstood your question and this was not the direction you want, I apologize.
If you want to select the columns you want to put, please modify above sample script as follows.
var csvData = Utilities.parseCsv(csvBlob[0].getDataAsString());
var sheet = SpreadsheetApp.getActiveSheet();
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();
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.
var csvData = Utilities.parseCsv(csvBlob[0].getDataAsString());
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
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());
Upvotes: 2