I am new to GAS and I am struggling badly with the problem that I have. (I haven't found a similar question on the site that would have solved my problem, therefore I am asking a new one)
Goal: Import CSV from Google Drive into Google Sheets
Problem:
Currencies in the csv file are "1,000.57" --> US format
Currency format that I need "1.000,57" --> European format
Currently with the Utilities.parseCsv() the formats just gets messed up and the currencies are plain wrong.
Question: Is there a way to change "," to "." and "." to "," during the parse? If so, will there be further problems since the delimiter for the csv is "," as well.
I already found some code snippets in the web (not my code: props to spreadsheet.dev) and tried to change the following, but it does not seem to work:
//Imports a CSV file in Google Drive into the Google Sheet
function importCSVFromDrive() {
var fileName = promptUserForInput("Please enter the name of the CSV file to import from Google Drive:");
var files = findFilesInDrive(fileName);
if(files.length === 0) {
displayToastAlert("No files with name \"" + fileName + "\" were found in Google Drive.");
return;
} else if(files.length > 1) {
displayToastAlert("Multiple files with name " + fileName +" were found. This program does not support picking the right file yet.");
return;
}
var file = files[0];
var csvString = file.getBlob().getDataAsString()
var escapedString = csvString.replace(",",".")
.replace(".",",");
var contents = Utilities.parseCsv(escapedString);
var sheetName = writeDataToSheet(contents);
displayToastAlert("The CSV file was successfully imported into " + sheetName + ".");
}
//Prompts the user for input and returns their response
function promptUserForInput(promptText) {
var ui = SpreadsheetApp.getUi();
var prompt = ui.prompt(promptText);
var response = prompt.getResponseText();
return response;
}
//Returns files in Google Drive that have a certain name.
function findFilesInDrive(filename) {
var files = DriveApp.getFilesByName(filename);
var result = [];
while(files.hasNext())
result.push(files.next());
return result;
}
//Inserts a new sheet and writes a 2D array of data in it
function writeDataToSheet(data) {
var ss = SpreadsheetApp.getActive();
sheet = ss.insertSheet();
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
return sheet.getName();
}
What am I doing wrong?