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

Question

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.

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

question from:https://stackoverflow.com/questions/65917579/real-limit-of-importdata-seems-lower-than-advertised-testing-importdata-with-1

Answer 1

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.