CODEWITHSUNDEEP
csvgoogle-apps-scriptgoogle-sheets
Kate Bedrii
Kate Bedrii

Reputation: 103

Filtering .csv data before import (app script)

I have a script that imports data from a .csv file daily. The .csv is pretty big (>20k rows), and it causes a huge load on the spreadsheet and sometimes results in script timeout.

I figured out that I don`t actually need all the 20+k rows but only those with date value in Column I > 2020-09-01. I tried several options with building in the filter in my script with no luck. I would highly appreciate any help or guidance on how to filter the .csv data by column prior to importing it to a spreadsheet.

The script I`m using:

  function importOP() {
  var file = DriveApp.getFilesByName("OP_Data_Daily.csv").next();
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString('ISO-8859-1'),'\t');
  var sheet = SpreadsheetApp.getActive().getSheetByName('OP');
  sheet.clear();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  }

Upvotes: 0

Views: 1452

Answers (2)

Yuri Khristich
Yuri Khristich

Reputation: 14537

Not an answer yet. Just a note.

I think you imported your csv data with wrong setting.

Your table looks like this:

enter image description here

But I believe it should look like this:

enter image description here

Since a default separator is comma ,. But a separator in your data is a semi-colon ;. You need to indicate it explicitly during import:

enter image description here

If you made the table with the script in OP you need to change this line:

var csvData = Utilities.parseCsv(file.getBlob().getDataAsString('ISO-8859-1'),'\t');

with this

var csvData = Utilities.parseCsv(file.getBlob().getDataAsString('ISO-8859-1'),';');

But this is not an answer. Not yet. You still need a script that will crop your data.

Update

I just tried to make filter via simply script on your full csv date and it works more or less. It freezes a bit, but works.

Here is the my script:

function importOP() {
  var file = DriveApp.getFilesByName("OP_Data_Daily.csv").next();
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString('ISO-8859-1'),';');
  var sheet = SpreadsheetApp.getActive().getSheetByName('OP');
  sheet.clear();
  try { sheet.getFilter().remove() } catch(e) {}
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  var filter = sheet.getFilter() || sheet.getRange('I1:I').createFilter();
  var date = new Date("2020-09-01");
  var criteria = SpreadsheetApp.newFilterCriteria().whenDateAfter(date).build();
  filter.setColumnFilterCriteria(9, criteria);
}

enter image description here

Update 2

Okay, if you insist to paste filtered data in the table you can do it pretty easy this way:

function importOP_filtered() {
  var file = DriveApp.getFilesByName("OP_Data_Daily.csv").next();
  var date = new Date("2022-09-01");
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString('ISO-8859-1'),';');

  csvData = csvData.filter(row => new Date(row[8]) > date); // <-- here is the filter

  var sheet = SpreadsheetApp.getActive().getSheetByName('OP');
  sheet.clear();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

Upvotes: 1

azawaza
azawaza

Reputation: 3084

Something like this should do the trick, I think...

var csvData = Utilities.parseCsv(file.getBlob().getDataAsString('ISO-8859-1'),'\t')
  .filter(function(row, i){
    return i===0 || parseInt(row[8].split('-').join(''))>20200901
  });

Basically, it applies a filter() method to the 2D array returned by parseCsv(). The filter uses a function to return only: the first row from the csv (the headers row) plus any row where the value of the 9th item (parsed as integer after removing all -) is greater than 20200901.

Upvotes: 2

Related Questions