CODEWITHSUNDEEP
csvgoogle-apps-scriptgoogle-sheets
Casper
Casper

Reputation: 1435

Importing CSV in GAS - too slow

I have a script that imports a CSV file. It works, but it's really slow in my opinion. Since the CSV file is pretty big the script exceeds the maximum execution time.

Does anyone have suggestions on how to increase the speed of my code?

FYI: Due to the S3 authentication I cannot use a built-in Sheets function.

function myFunction() {

  var liquidityBalance = SpreadsheetApp.openByUrl(liquidityBalanceSheet).getSheetByName('Liquidity');
  var s3 = S3.getInstance(awsAccessKeyId, awsSecretKey);

  var fromS3 = s3.getObject(bucket, file);
  var data = Utilities.parseCsv(fromS3.getDataAsString(), '\t');
  var csv = []
  liquidityBalance.clearContents();
  for (var i = 0; i < data.length; i++) {
    var row = []
    for (var a = 0; a < data[i].length; a++) {
      row.push(data[i][a]);
    }
    liquidityBalance.appendRow(row);
  }
}

Upvotes: 0

Views: 267

Answers (1)

Alan Wells
Alan Wells

Reputation: 31300

Hopefully the following code will run faster. This code assembles all the data and then writes the assembled data in one action instead of appending rows one at a time. If the code still times out, then you will need to run partial amounts of data and know where you left off last to continue the next batch.

function myFunction() {
  var a,csv,data,fromS3,i,L,L2,lastRow,liquidityBalance,
      outerArray,row,s3,thisRow;

  liquidityBalance = SpreadsheetApp.openByUrl(liquidityBalanceSheet).getSheetByName('Liquidity');
  s3 = S3.getInstance(awsAccessKeyId, awsSecretKey);

  fromS3 = s3.getObject(bucket, file);
  data = Utilities.parseCsv(fromS3.getDataAsString(), '\t');
  csv = [];

  lastRow = liquidityBalance.getLastRow();

  liquidityBalance.clearContents();

  outerArray = [];

  L = data.length;
  L2 = data[0].length;//Number of elements in an inner array

  for (i = 0; i < L; i++) {
    row = [];//reset on every loop
    thisRow = data[i];

    for (a = 0; a < L2; a++) {
      row.push(thisRow[a]);
    }

    outerArray.push(thisRow);
  }

  liquidityBalance.getRange(lastRow + 1, 1, outerArray.length, L2).setValues(outerArray);
}

Upvotes: 1

Related Questions