How Can I Preserve Existing Formatting on a Google Sheets Apps Script CSV Import

Question

On repeated imports into the same sheet, most formatting is preserved (e.g. colors) but some is not (e.g. setting a column to % format). How can I preserve all formatting already in the sheet prior to repeated csv imports, e.g. a script as follows that repeatedly updates the sheet (where "file" is a csv file on a Google Drive):

var content = file.getBlob().getDataAsString();  # csv file on a Google Drive
var csvData = Utilities.parseCsv(content);
current_sheet.clearContents();
current_sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

"file" (which becomes `content`) is a csv file on a Google Drive. — Kim Miller, Jun 28 '21 at 14:56

score 1 · Answer 1 · answered Jun 28 '21 at 16:22

SOLVED: With the help of this post: Copy value and format from a sheet to a new google Spreadsheet document?

The first and last lines are the solution:

var sNF = current_sheet.getDataRange().getNumberFormats();

var content = file.getBlob().getDataAsString();  # csv file on a Google Drive
var csvData = Utilities.parseCsv(content);
current_sheet.clearContents();
current_sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

current_sheet.getDataRange().setNumberFormats(sNF)

score 0 · Answer 2 · answered Jun 25 '21 at 19:42

I just did a quick test with the script below and the cells formatting was preserved.

function importCSVFromWeb() {
  // Provide the full URL of the CSV file.
  var csvUrl = "https://query1.finance.yahoo.com/v7/finance/download/MSFT?period1=1577806579&period2=1609428979&interval=1d&events=history&includeAdjustedClose=true";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clearContents();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

There is a slight difference in the way I fetched the data

While I like the general approach, `getContentText()` does not seem available to my fetch method, which is `file.getBlob().getDataAsString();` — Kim Miller, Jun 25 '21 at 21:51

How Can I Preserve Existing Formatting on a Google Sheets Apps Script CSV Import

2 Answers2