I'm working to automate the flow of data that we use to power some of our analytics/reporting. To summarize, I have a CSV in Google Drive that needs to be imported into a Google Sheet. The CSV contains line breaks in some of the "cells" that is causing the import to be completely askew / out of line. To be clear, I can get the data, but it is misaligned due to the line breaks in the CSV.
I started with code from Ctrl:
function importCSVFromGoogleDrive() {
var file = DriveApp.getFilesByName("tweet_activity.csv").next();
var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}
I quickly discovered that, while the code did import the file, the line breaks ruined the alignment of the data. Upon inspection, it's clear that the line breaks are the genesis of the issue.
I scoured the forums and found some possible fixes ([here](Saving as CSV through google script handling newline characters ) and [here](Apps Script Utilities.parseCsv assumes new row on line breaks within double quotes ) for instance)
I've tried:
function importCSVFromGoogleDrive_v2() {
var file = DriveApp.getFilesByName("tweet_activity.csv").next();
var NewFile = file.replace("\n", " ").replace("\r", " ");
var csvData = Utilities.parseCsv(NewFile.getBlob().getDataAsString());
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}
This resulted in "TypeError: Cannot find function replace in object tweet_activity.csv. (line 42, file "Code")"
I also tried replacing the line breaks after the parse.
function importCSVFromGoogleDrive_v3() {
var file = DriveApp.getFilesByName("tweet_activity_metrics_downtownstlouis_20190303_20190331_en.csv").next();
var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
var csvDataRev = csvData.replace("\n", " ").replace("\r", " ");
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}
This resulted in "TypeError: Cannot find function replace in object"
Finally, I tried different replace code (I modified for my purposes, but this was the pertinent part):
var dataString = myBlob().getDataAsString();
var escapedString = dataString.replace(/(?=["'])(?:"[^"\](?:\[\s\S][^"\])"|'[^'\]\r\n(?:\[\s\S][^'\]\r\n)')/g, function(match) { return match.replace(/\r\n/g,"\r\n")} );
var csvData = Utilities.parseCsv(escapedString);
No dice.
Any suggestions on how to deal with the line breaks in this scenario?