How replace string while importing csv by link, in Google Spreadsheet, using App Scripts?

Question

In google App Script, while trying to import via link a csv into a spreadsheet, I'd like to modify a string to correctly run Utilities.parseCsv, but the replace doesn't seem to work. Where am I wrong? I specify that in the csv all the strings are in double quotes like:

name, color, age
"Mary","red","33"
"Paul","blue","34"
"John","black","35"

the csv I need to import is this:

https://raw.githubusercontent.com/teamdigitale/padigitale2026-opendata/main/data/candidature_scuole_finanziate.csv

but if I try to import it throws me Exception: Could not parse text.

in the csv there are lines like this:

"istsc_tpis008004","POLO STATALE DI ISTRUZIONE SECONDARIA SUPERIORE "P.MATTARELLA"","Castellammare del Golfo","081005","TP","081","Sicilia","19",7301.0,"1.4.1 Esperienza del Cittadino - Scuole - Aprile 2022","2022-06-20T10:22:19.000+0000","2022-08-19","J21F22001670006",2.0,33.0,"A"

with double quotes next to it "" which I think creates the problem.

that's why i want to edit the csv between var csvContent = UrlFetchApp.fetch(csvUrl).getContentText(); and var csvData = Utilities.parseCsv(csvContent,',');

A thousand thanks

  function uploadCsv(csvUrl, sheetName, row, column) {
    var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
    csvContent.replace(/stringToModify/g, "modifiedString");
    var csvData = Utilities.parseCsv(csvContent, ',');
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    sheet.getRange(row, column, csvData.length, csvData[0].length).setValues(csvData);
  }

I'm not sure of your question. You don't need to modify any string. Your file as is will translate to ['Mary','red','33'] which when using setValues will become in the cells a string,string,number Mary,red,33 — TheWizEd
– TheWizEd, Commented Mar 6, 2023 at 15:07
I can't determine exactly what is wrong with your data but some lines parse as 16 values, some as 17 and some as 18. All records have to be the same length. — TheWizEd
– TheWizEd, Commented Mar 6, 2023 at 20:57

Tanaike · Accepted Answer · 2023-03-07 00:37:29Z

In your situation, for example, how about using Sheets API? I thought that the CSV parser of Sheets API might be stronger than Utilities.parseCsv. So, please test the following sample script.

Sample script:

Before you use this script, please enable Sheets API at Advanced Google services.

function uploadCsv() {

  var csvUrl = "https://raw.githubusercontent.com/teamdigitale/padigitale2026-opendata/main/data/candidature_scuole_finanziate.csv";
  var sheetName = "Sheet1";
  var row = 1;
  var column = 1;

  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  const requests = { requests: [{ pasteData: { delimiter: ",", data: csvContent, coordinate: { sheetId: sheet.getSheetId(), rowIndex: row - 1, columnIndex: column - 1 } } }] };
  Sheets.Spreadsheets.batchUpdate(requests, SpreadsheetApp.getActiveSpreadsheet().getId());
}

When this script is run, the CSV data from csvUrl is put from the cell "A1" of "Sheet1".
When I tested this script, it seems that the CSV data is put into the sheet. But, I'm not sure whether that is your expected result. So, please confirm it.

Collectives™ on Stack Overflow

How replace string while importing csv by link, in Google Spreadsheet, using App Scripts?

1 Answer 1

Sample script:

References:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample script:

References:

Comments

Your Answer

Sign up or log in

Post as a guest

Related