CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Doug Fir
Doug Fir

Reputation: 21282

Script to import csv file gives funny characters including question marks in a black diamond

I'm attempting to recycle an apps script I used several months ago and am running into problems.

Within the drive directory in question there are two files: leads_data.csv; LeadsReport (Google Sheet).

My goal is to import data from the csv file into LeadsReport using a script.

Here is what the csv file looks like in a text editor: enter image description here

So the data do appear to be separated with a comma.

Here is my apps script:

// add the CSV menu. Might change this to be an automatic update base don date
function onOpen() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
  var csvMenuEntries = [{name: "Update Data", functionName: "importFromCSV"}];
  ss.addMenu("Update", csvMenuEntries);
}

function importFromCSV() {
      var file = DriveApp.getFilesByName("leads_data.csv");// get the file object
      var csvFile = file.next().getBlob().getDataAsString();// get string content
      Logger.log(csvFile);// check in the logger
      var csvData = CSVToArray_(csvFile);// convert to 2D array
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName('DataImport'); // only add data to the tab DataImport to prevent overwriting other parts of the spreadsheet
      sheet.getRange(2,1, csvData.length, csvData[0].length).setValues(csvData);// write to sheet in one single step. Start at row 2 (getRange(2... )
      SpreadsheetApp.getUi().alert('Data Updated');
    }

    function CSVToArray_(strData){
     var rows = strData.split("\r");
  // Logger.log(rows.length);
     var array = [];
      for(n=0;n<rows.length;++n){
       if(rows[n].split(',').length>1){
         array.push(rows[n].split(','));
       }
       }
    Logger.log(array);
    return array;
      
    }

When I save and then select the new menu item "Update" I get two unexpected outcomes:

  1. "Incorrect range width, was 10 but should be 5"
  2. There are two rows populated with symbols I don't recognise. The expected result was that there would be a table of data with 7 columns and ~ 3,000 rows. See picture below. enter image description here

From previous experience I tried changing between:

var rows = strData.split("\r");
var rows = strData.split("\n");

In each case I got similar results.

Can anyone see why my script is importing the data in this way?

Upvotes: 1

Views: 1779

Answers (1)

Spencer Easton
Spencer Easton

Reputation: 5782

Use the built in csv parser. Look at the snipped below.

https://developers.google.com/apps-script/reference/utilities/utilities#parseCsv(String)

function myFunction() {
  var file = DriveApp.getFilesByName('CCStandards - English Language Arts & Literacy.csv').next();
  Logger.log(Utilities.parseCsv(file.getBlob().getDataAsString()));
}

// Your code adapted below

function importFromCSV() {
  var file = DriveApp.getFilesByName("leads_data.csv");// get the file object
  var csvFile = file.next().getBlob().getDataAsString();// get string content
  var csvData = Utilities.parseCsv(csvFile);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('DataImport'); // only add data to the tab DataImport to prevent overwriting other parts of the spreadsheet
  sheet.getRange(2,1, csvData.length, csvData[0].length).setValues(csvData);// write to sheet in one single step. Start at row 2 (getRange(2... )
  SpreadsheetApp.getUi().alert('Data Updated');
}

Upvotes: 1

Related Questions