Import from CSV is very slow

Question

My script (see below) is very slow while i'm importing a csv table into Google Sheets. Anyone have an idea where the problem is?

I tried it already on different accounts

Greetings

function import_konto_kst_statistik_budget() {
  var fSource = DriveApp.getFolderById('1B2t86GVMoB4yilJ5ieWuGpxFYzsVm8bs'); // reports_folder_id = id of folder where csv reports are saved
  var fi = fSource.getFilesByName('konto_kst_statistik_budget.csv'); // latest report file
  var ss = SpreadsheetApp.getActive()
  var sheet = ss.getSheetByName('90_konto_kst_statistik_budget'); // Select Sheet Konto
  sheet.activate()  // aktiviert das Sheet Konto
  sheet.clear()  // löscht alle Inhalte auf Sheet Konto

  if ( fi.hasNext() ) { // proceed if "konto.csv" file exists in the reports folder
    var file = fi.next();
    var csv = file.getBlob().getDataAsString();
    csv = csv.replace(/'/g, '"');
    var csvData = CSVToArray(csv); // see below for CSVToArray function

    // loop through csv data array and insert (append) as rows into 'NEWDATA' sheet
    for ( var i=0, lenCsv=csvData.length; i<lenCsv; i++ ) {
      sheet.getRange(i + 1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
    }
  }
}

It does not really matter the speed of the transfer, unless it is for active calculations. — , Mar 03 '18 at 16:24
related: https://stackoverflow.com/questions/49020131/how-much-faster-are-arrays-than-accessing-google-sheets-cells-within-google-scri/49020786#49020786 — tehhowch, Mar 03 '18 at 16:32

Jack Brown · Answer 1 · 2018-03-03T23:50:38.207

2

Your script is slow due to this loop:

for ( var i=0, lenCsv=csvData.length; i<lenCsv; i++ ) {
      sheet.getRange(i + 1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
  }

change it to:

sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

Reason:
Write operations using setValues have huge overhead and take longer, so try to write your data in one go where ever possible.

Edit:
If the reason you input one row at a time is that you have a variable length of the column in your CSV data. I would suggest modifying your CSVtoArray() function to return an array with uniform column length

edited Mar 03 '18 at 23:50

answered Mar 03 '18 at 16:28

Jack Brown

5,802
2
12
27

What is ``i`` at ``csvData[i].length`` of changed one? If ``CSVtoArray()`` has uniform column length, is it ``0`` instead of? – Elsa Mar 03 '18 at 23:38
it should be csvData[0].length, just modified it – Jack Brown Mar 03 '18 at 23:51
Thanks for your advice i get an error with your code (sorry is in german) "Falsche Bereichsbreite, war 4, sollte jedoch 1 sein. (Zeile 159, Datei "Code")Schließen" Translated with google Translator Wrong area width was 4, but should be 1. (Line 159, file "Code") Close – Kreison GmbH Mar 04 '18 at 20:51
I figured as much, the array length are not equal. If you look at the spreadsheet sheet atleast the first row would be written into. – Jack Brown Mar 04 '18 at 20:54
Could you post your csvtoarray() function and an example csv file to verify the same. – Jack Brown Mar 04 '18 at 20:56

Import from CSV is very slow

1 Answers1