Google sheet import selected columns from csv

Question

I am trying to import only few columns from a CSV in a google sheet. My CSV is huge (26k lines) and I only need 4 columns of the 32 that are imported. The following script (that I found in the forum) is used to import the CSV into GoogleSheet:

function importReport() {

  var threads = GmailApp.search('label:stock_fastmag subject:"Rapport de commande - article detail - TOTAL"');
  var message = threads[0].getMessages()[0];
  var attachment = message.getAttachments()[0];
  attachment.setContentType('text/csv');

 // Is the attachment a CSV file
 if (attachment.getContentType() === "text/csv") {

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Reliquat_commandes_clients");
    var csvData = Utilities.parseCsv(attachment.getDataAsString(), ";");

  // Remember to clear the content of the sheet before importing new data
   sheet.clearContents().clearFormats();
   sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

  }
}

The problem is that as the script can not run entirely, the authorized execution time is too short. Do you think that if I import only some columns of the CSV it should make it quicker?

Possible duplicate of [Downloading Big CSV Files and putting in Google Sheet - Google App Script](https://stackoverflow.com/questions/54106041/downloading-big-csv-files-and-putting-in-google-sheet-google-app-script) — TheAddonDepot, Jan 25 '19 at 14:11
If you write less data, yes, it will execute more quickly. But is the writing portion the bottleneck? Or is it the parsing? If the problem is in the parsing, then it doesn't matter how much data you're writing, since the script didn't reach that part. — tehhowch, Jan 25 '19 at 14:44
@DimuDesigns yes indeed, I tried this method with the same result — Benoit Gramond, Jan 28 '19 at 14:43
@tehhowch true. I need to know when it occurs. The only error I have is; 26/01/19 04:03 importReport Durée d'exécution autorisée dépassée time-based 26/01/19 04:33 ; no more explainations — Benoit Gramond, Jan 28 '19 at 14:45
You can use the Execution Transcript, or manually time sections with the Stackdriver methods `console.time`/`timeEnd` if you want to track more than the last execution. Of particular interest are the `getAttachments()[0]`, `getDataAsString`, `parseCsv`, and `setValues` function calls, since these are size-dependent — tehhowch, Jan 28 '19 at 15:33

score 0 · Answer 1 · answered Jan 25 '19 at 16:52

It is most likely writing to the sheet that is taking the most time. I set up a simple test with a CSV file of my own with 50,000 lines and 32 columns with each cell containing an 8 character string. It is just under 50mb, the limit for DriveApp to open the file.

Opening and parsing the file took 12 seconds. Opening, parsing, and writing to a sheet took 200 seconds. Extracting just the necessary 4 columns and writing to a sheet took only 34 seconds so limiting what you write is definitely a way to improve performance.

I wasn't able to create a situation where I exceeded the maximum execution time because of the file size limitation with DriveApp but this appears to be a good technique to employ to overcome the issue: Exceeded Max Execution Time in Google Apps Script.

Hi @BMcV, nice ink, I will see what I can do with it. It is weired; my CSV file pmake the import in more than 1800 sec then expires — Benoit Gramond, Jan 28 '19 at 14:51

Google sheet import selected columns from csv

1 Answers1