Can I preserve custom data formats in a Google sheet when I import data into it from a CSV?-CodePudding

I am creating a Google sheet that intends to receive a CSV import. I have manually created number formats for each cell (date, number, text, etc) and I would like the import to respect that formatting. At the moment, that is not the case. Here is my workflow:

Select cell A1
File > Import > Select CSV file
Set Import Location to "Replace data at selected cell"
Disable "Convert text to numbers, dates, and formulas"
Click "Import data"

All the data comes in with number formats set to "Automatic" and displaying as text.

Additional comments:

In step #3, I've also tried "Replace current sheet", but that doesn't work either
In step #4, if I don't choose to disable this checkbox, I will lose leading zeroes on one of my columns, which is not acceptable

CodePudding user response：

I believe your current situation and your goal are as follows.

You have a sheet on a Google Spreadsheet. The cells of the sheet have the number formats you manually set.
You want to put CSV data on this sheet.
When the CSV data is put to the sheet, you want to keep the manually set number formats of the cells.

I had had the same issue with your situation. In this case, when the CSV data is manually put using the default UI, it seems that the number formats of all cells cannot be kept. So, at that time, I used a workaround. This workaround uses Google Apps Script. When Google Apps Script is run, a dialog is opened. And, the CSV file is loaded, and then, the CSV data is put to the active cell with keeping the number formats.

When this workaround is reflected in a Google Apps Script, it becomes as follows.

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:

CodePudding user response：

Don't disable conversion of dates and numbers, it cant format a string into a date unless you tell it to.

CodePudding user response：

This uses a hidden sheet to preserve any sheet formats. The hidden sheet is created by selecting 'Formats -> Lock Formats' from the menu. Anytime the sheet is changed, the formats from the hidden sheet will be re-applied.

const FORMAT_SHEET_NAME = 'format-sheet';

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Formats')
    .addItem('Lock Formats', 'lockFormats')
    .addToUi();
}

function onEdit(event) {
  const formatSheet = SpreadsheetApp.getActive().getSheetByName(FORMAT_SHEET_NAME);

  if (formatSheet) {
    const activeRange = event.source.getActiveRange();
    const activeRangeA1Notation = activeRange.getA1Notation();
    const activeRangeFormats = activeRange.getNumberFormats();
    const formatSheetFormats = formatSheet.getRange(activeRangeA1Notation).getNumberFormats();

    if (haveSameFormats(activeRangeFormats, formatSheetFormats) === false) {
      activeRange.setNumberFormats(formatSheetFormats);
    }
  }
}

function haveSameFormats(aFormats=[], bFormats=[]) {
  const aFormatsNormalized = aFormats.flat();
  const bFormatsNormalized = bFormats.flat()

  return aFormatsNormalized.every((item, index) => item === bFormatsNormalized[index]);
}

function lockFormats() {
  const spreadsheet = SpreadsheetApp.getActive();
  const sheet = spreadsheet.getActiveSheet();
  const dataRange = sheet.getDataRange();
  const dataRangeA1Notation = dataRange.getA1Notation();
  const startRowIndex = dataRange.getRowIndex();
  const startColIndex = dataRange.getColumn();
  const lastRowIndex = dataRange.getLastRow();
  const lastColIndex = dataRange.getLastColumn();

  const numberFormats = dataRange.getNumberFormats();
  let formatSheet = spreadsheet.getSheetByName(FORMAT_SHEET_NAME);
  
  if (formatSheet) {
    spreadsheet.deleteSheet(formatSheet);
  }

  formatSheet = spreadsheet.insertSheet(FORMAT_SHEET_NAME);
  formatSheet.hideSheet();

  formatSheet.getRange(dataRangeA1Notation).setValues(numberFormats);
  
  dataRange.copyFormatToRange(
    formatSheet, 
    startColIndex, 
    lastColIndex, 
    startRowIndex, 
    lastRowIndex
  );

  const protection = formatSheet.protect();
  const user = Session.getEffectiveUser();
  protection.addEditor(user);
}