Parse CSV number as text

Question

I am trying to import the following CSV data into a Google Sheet, preserving the number format "0001,0002,0003" as a string.

CSV file

0001,0002,0003
test,test2,test3

I tried using Tanaike's code:

Google Sheets Import CSV popup

function importCsv(e){
  if (!e) {
    SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile("index"), "sample");
    return;
  }
  const csv = Utilities.parseCsv(Utilities.newBlob(...e).getDataAsString());
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(sheet.getLastRow() + 1, 1, csv.length, csv[0].length).setValues(csv);
}

But also the Batch Update one: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate

function importCsvbatchUpdate(e){
  
    if (!e) {
    SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile("index"), "sample");
    return;
    }

var data = Utilities.parseCsv(Utilities.newBlob(...e).getContentText());

  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      sheet = ss.getActiveSheet();


var resource = {
  requests: [
    {
      pasteData: {
        data: data,
        coordinate: { sheetId: sheet.getSheetId() },
        delimiter: ","
      }
    }
  ]
};
Sheets.Spreadsheets.batchUpdate(resource, ss.getId());

Both work as expected, but unfortunately, the "0001,0002,0003" in the CSV files are systematically populated as numbers "1,2,3" in Google Sheets.

Any ideas on how to keep the text/string formats of values with prefixed zeros?

Answer 1

In your situation, how about the following modification?

Modified script:

function importCsv(e){
  if (!e) {
    SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile("index"), "sample");
    return;
  }
  const csv = Utilities.parseCsv(Utilities.newBlob(...e).getDataAsString());
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(sheet.getLastRow() + 1, 1, csv.length, csv[0].length).setNumberFormat("@").setValues(csv); // Modified
}

• By this modification, the values are put into cells as strings. By this, 0001 is put as 0001.

Note:

• From as I understand it each element of the csv comes in the form of triplets like this "0001,0002,0003" in the Cooper's comment, when I saw this question, I guessed that from Both work as expected, but unfortunately, the "0001,0002,0003" in the CSV files are systematically populated as numbers "1,2,3" in Google Sheets. and the sample CSV data, 0001,0002, and 0003 might be each cell. When "0001,0002,0003" is included in the CSV data, I think that 0001,0002,0003 will be put into a cell. But, if "0001,0002,0003" is included in your CSV data, please test the following modification. In this case, I think that both patterns can be used.

  function importCsv(e) {
    if (!e) {
      SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile("index"), "sample");
      return;
    }
  
    const temp1 = Utilities.parseCsv(Utilities.newBlob(...e).getDataAsString()).map(r => r.flatMap(c => c.split(",")));
    const maxLen = Math.max(...temp1.map((r) => r.length));
    const csv = temp1.map((r) => [...r, ...Array(maxLen - r.length).fill(null)]);
  
    const sheet = SpreadsheetApp.getActiveSheet();
    sheet.getRange(sheet.getLastRow() + 1, 1, csv.length, csv[0].length).setNumberFormat("@").setValues(csv);
  }
  

Reference:

• setNumberFormat(numberFormat)