CODEWITHSUNDEEP
excelcsvgoogle-apps-scriptgoogle-sheets
Daniel
Daniel

Reputation: 23

CSV conversion script to Google sheet format is not behaving correctly

I'm, currently using this automated script to convert automatically a dynamic csv sheet to google format, for further modification.

function importCSVFromGoogleDrive() {
  var file = DriveApp.getFilesByName('Inventory.csv').next();
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

While the script works most of the time, however, occasionally instead of replacing the initial data, it will instead add a copy of the rows, leading to duplication.

This is the data how it supposed to be:

 House Number   Color   Family  Complaints 
 22             Red     Smiths  4
 A65            Blue    Stefans 5 
 18             Yellow  Dolmens 2

In perfect world, every time the script is activated, it will pull data from the dynamic csv and replace the existing data.

However, occasionally After a day of the script working every minute or 5 minutes. I find it in this state:

House Number    Color   Family  Complaints 
   22           Red     Smiths  4
   A65          Blue    Stefans 5 
   18           Yellow  Dolmens 2 
   22           Red     Smiths  4 
   A65          Blue    Stefans 5 
   18           Yellow  Dolmens 2 
   22           Red     Smiths  5 
   A65          Blue    Stefans 5 
   18           Yellow  Dolmens 3

As you can see instead of modifying the existing rows, occasionally it creates new set of rows for the data. Now this happens rarely, as after a day of running the script every 5 minutes, I only had perhaps 4-5 additional sets of rows.

You will notice that in the example, in the last set of rows the data varies, that just to show that due to the nature of the dynamic csv, the data will change. However, it should be overwriting the original, as opposed to, again, creating new sets of rows.

What would be the proper solution to this?

Upvotes: 1

Views: 57

Answers (1)

RemcoE33
RemcoE33

Reputation: 1620

Don't know for sure why it fails but maybe this is the solution. So we parse the csv ourselves.

The splitCsv() function is form another post here in SO. I cannot seem to find it...

function importCSVFromGoogleDrive() {
  const file = DriveApp.getFilesByName('Inventory.csv').next();
  const csv = file.getBlob().getDataAsString()
  const csvData = csv.split("\n").map(row => splitCsv(row));
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

/**
 * Splits the csv line to columns and escaping the comma's inside double qoutes.
 * @param {string} str the csv line string.
 * @returns {array} array of columns
 */
function splitCsv(str) {
  return str.split(",").reduce(
    (acc, cur) => {
      if (acc.isConcatting) {
        acc.soFar[acc.soFar.length - 1] += "," + cur;
      } else {
        acc.soFar.push(cur);
      }
      if (cur.split('"').length % 2 == 0) {
        acc.isConcatting = !acc.isConcatting;
      }
      return acc;
    },
    { soFar: [], isConcatting: false }
  ).soFar;
}

Upvotes: 1

Related Questions