Real limit of IMPORTDATA seems lower than advertised (testing importdata with 1.5 mln cells)

user_10967095 0
Viewed 1049

I try to import data from a .csv file located on my Google Drive. It's a csv file with ; as separators. Filesize is about 4 MB. 46 columns and 8770 rows. It is possible to upload it manually to my spreadsheet but I need it to be connected via importdata or similar function and update.

When I try to import the file I get a message that contents of url exceeds maximum size. Trimming it with nested array_constrain formula does not help.

My spreadsheet contains some sensitive data so I decided to make a dummy csv to illustrate the problem and see if you have the same:

Here is an array of random numbers 50 000 rows and 30 columns (1 500 000 cells). It is a 5,61 MB file. Download link I use is: https://drive.google.com/uc?export=download&id=19MBtGO-O7PV4NNojLAcxm-Pg8ZwjFXI8

File is located here: https://drive.google.com/file/d/19MBtGO-O7PV4NNojLAcxm-Pg8ZwjFXI8/view?usp=sharing

When I try to refer to it using IMPORTDATA it tells it exceeds size, but it can be uploaded manually via menu.

enter image description here

You can play here with this file: https://docs.google.com/spreadsheets/d/16FprlLCST98CpB09CvtTK_CeSd76uUCsRRHWBF3bbMY/edit?usp=sharing

According to this discussion it should work anyway up to 2 000 000 cells: https://webapps.stackexchange.com/questions/10824/whats-the-biggest-csv-file-you-can-import-into-a-google-sheets

google-drive-api google-apps-script google-sheets csv
1 Answers

If noone comes up with a google sheets formula solution, then you can simply use Utilities.parseCsv(csv):

function getBigCsv() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Arkusz2');
  const url = 'https://drive.google.com/uc?export=download&id=19MBtGO-O7PV4NNojLAcxm-Pg8ZwjFXI8';
  const csv = UrlFetchApp.fetch(url);
  const data = Utilities.parseCsv(csv);
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}

and if you want the data to be updated, you can set up a time-trigger to refresh the data.

user_112252910
Related