How to remove unwanted character from string in Google sheet

Question

I am trying to do a bit of cleaning up on some data that I have imported from a csv file. I figured out how to get the formatting for some of the columns how I need it but am struggling with the double quotes that remain on either end of each row. I assume the quotes are being added as a result of the getDataAsString() function.

As you can see from the below I am trying to use replace() to get rid of the double quote from the values in the last column. The logger shows the data without the ", but the line I have used is not having any effect on the data in the sheet. I suspect this is because all I am doing with the script that I have so far, is saying "show me the value from the last column without the quote" but not actually updating the data.

How can I convert the "show" line to an actual change data line?

function importFromCSV() {
  var file = DriveApp.getFileById(listLatestFile());
  //var csvFile = file.getBlob().getDataAsString();// get string content
  var csvFile = file.getAs('text/plain').getDataAsString();
  //Logger.log(csvFile);// check in the logger
  var csvData = CSVToArray_(csvFile);// convert to 2D array
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("ImpCSVData");
      sheet.clear();
      sheet.getRange(1,1, csvData.length, csvData[0].length).setValues(csvData);// write to sheet in one single step
      var test = sheet.getRange(1,csvData[0].length, csvData.length, 1).getValue().replace("\"","");
      Logger.log(test);
      sheet.getRange(1,4, csvData.length, 2).setNumberFormat("DD/MM/YYYY");
      sheet.getRange(1,6, csvData.length, 1).setNumberFormat("@STRING@");
      sheet.getRange(1,csvData[0].length, csvData.length, 1).getValue().replace("\"","");
  }

Answer 1

...the line I have used is not having any effect on the data in the sheet

In the code snippet you've provided, there is no setValue() or setValues() call being made. Without that, you aren't making any changes to the content of the spreadsheet. Say you have a variable, text, that you want to write to the sheet. You should then do this:

  sheet.getRange(1,csvData[0].length, csvData.length, 1).setValue(text);

The .getValue() method returns an object from the given range - your code assumes this is a string. If it isn't a string (say it's a date or number instead), then .replace() will throw an exception. Even if it is a string, this line will do nothing of use because you aren't storing the result in any way:

  sheet.getRange(1,csvData[0].length, csvData.length, 1).getValue().replace("\"","");

Try:

  var lastCellRange = sheet.getRange(1,csvData[0].length, csvData.length, 1);
  var text = lastCellRange.getValue().replace("\"","");
  lastCellRange.setValue(text);

Other comments:

• Your replace() statement will change the first occurrence of the double-quote character, regardless of where it appears. You want to replace leading or trailing quotes which may or may not be there, so try this instead (from this answer):

  var text = lastCellRange.getValue().replace(/^"?(.+?)"?$/,'$1');