Importing CSV Data by Keeping Number Formats of Cells on Google Spreadsheet using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

This is a sample script for importing a CSV data by keeping the number formats of cells on Google Spreadsheet using Google Apps Script.

When a CSV data is manually put using the default UI using the browser, it seems that the number formats of all cells cannot be kept. In order to import the CSV data to the cells with keeping the number formats, it is required to use a script as a workaround. In this workaround, Google Apps Script is used.

When the following sample script of Google Apps Script is run, a dialog is opened. And, when the CSV file is selected from your local PC on the dialog, the CSV file is loaded, and then, the CSV data is put to the active cell with keeping the number formats of cells.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet, and save the script. And, please run onOpen with the script editor. By this, the custom menu is created. After this, when you select a cell and run the script from run of the custom menu, the dialog is opened. And, when you select the CSV file, the CSV data is retrieved and put from the active cell. At that time, the CSV data is put while the number formats of the cells are kept.

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("sample")
    .addItem("run", "insertCSVdata")
    .addToUi();
}

function insertCSVdata(e) {
  if (!e) {
    const html = HtmlService.createHtmlOutput(
      `<input type="file" id="csv" value="Select CSV file" accept=".csv,text/csv" onchange="main()"><script>function main(){const file=document.getElementById("csv").files[0]; const fr=new FileReader(); fr.readAsArrayBuffer(file); fr.onload=(f)=> google.script.run.withSuccessHandler(google.script.host.close).insertCSVdata([[...new Int8Array(f.target.result)], file.name, file.type]);}</script>`
    );
    SpreadsheetApp.getUi().showModalDialog(html, "sample");
    return;
  }
  const activeRange = SpreadsheetApp.getActiveRange();
  const csv = Utilities.newBlob(...e).getDataAsString();
  const ar = Utilities.parseCsv(csv);
  const range = activeRange.offset(0, 0, ar.length, ar[0].length);
  const formats = range.getNumberFormats().map((r) => r.map((c) => c || "@"));
  range.setValues(ar).setNumberFormats(formats);
  return;
}
  • In this sample script, the number formats of the cells are copied and put the CSV data to the cells, and then, the copied number formats are pasted. By this flow, the number formats of cells are kept.

Note:

  • In this sample script, HTML and Javascript is as follows.

    <input type="file" id="csv" value="Select CSV file" accept=".csv,text/csv" onchange="main()">
<script>
function main() {
  const file = document.getElementById("csv").files[0];
  const fr = new FileReader();
  fr.readAsArrayBuffer(file);
  fr.onload = (f) => google.script.run.withSuccessHandler(google.script.host.close).insertCSVdata([[...new Int8Array(f.target.result)], file.name, file.type]);
}
</script>

References

 Share!