Keep the same style of a spreadsheet when exporting in excel

user_14680755 0
Viewed 156

With this script I could export a spreadsheet to a excel file keeping formulas and a specific range that I need.

But I need also to keep the style of the spreadsheet like colors, row height, text style...

What to add then to the code below?

 function downloadXLS_GUI() {
      var sh = SpreadsheetApp.getActiveSheet();
      var nSheet = SpreadsheetApp.create(sh.getName()+": copy");
      var numCols = 10;
      var d = sh.getRange(1,1,sh.getLastRow(),numCols);
      nSheet.getSheets()[0].getRange(1,1,sh.getLastRow(),numCols).setValues(d.getValues());
      ["A3", "C3", "F3", "G3", "H3", "F5", "G5", "H5"].forEach(a1Notation => {
        var sourceFormulas = sh.getRange(a1Notation).getFormulas();
        nSheet.getRange(a1Notation).setFormulas(sourceFormulas);
      });
      var URL = 'https://docs.google.com/spreadsheets/d/'+nSheet.getId()+'/export?format=xlsx';
      var htmlOutput = HtmlService
                      .createHtmlOutput('<a href="'+URL+'">Clicca qui per scaricare</a>')
                      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
                      .setWidth(380)
                      .setHeight(160);
      SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Scarica Allegato 2 in Excel');
    }
google-apps-script google-sheets excel
1 Answers
Update 2

For copying styles between different sheets you can use this method.

function downloadXLS_GUI() {
  const numCols = 10
  const sh = SpreadsheetApp.getActiveSheet();
  const nSheet = SpreadsheetApp.create(sh.getName() + ": copy");

  const o_range = sh.getRange(1, 1, sh.getLastRow(), numCols)
  const c_range = nSheet.getSheets()[0].getRange(1, 1, sh.getLastRow(), numCols)

  // Get the values and styles from the original
  const v = o_range.getValues()
  const b = o_range.getBackgrounds()
  const c = o_range.getFontColors()
  const f = o_range.getFontSizes()
  // Set the values and styles to the copy
  c_range.setValues(v)
  c_range.setBackgrounds(b)
  c_range.setFontColors(c)
  c_range.setFontSizes(f)
  const formulaA1Notation = ["A3", "C3", "F3", "G3", "H3", "F5", "G5", "H5"]
  formulaA1Notation.forEach(a1Notation => {
    var sourceFormulas = sh.getRange(a1Notation).getFormulas();
    nSheet.getRange(a1Notation).setFormulas(sourceFormulas);
  });
  var URL = 'https://docs.google.com/spreadsheets/d/' + nSheet.getId() + '/export?format=xlsx';
    SpreadsheetApp.getUi()
    .showDialog(
      HtmlService
        .createHtmlOutput(`<a href="${URL}" target="_blank">Download</a>`)
    )
}

Why not use the copy method?

Copies the spreadsheet and returns the new one.

Instead of copying a certain range, you copy it all and get rid of what you don't need.

For example:

Code.gs
function downloadXLS_GUI() {
  const ss = SpreadsheetApp
    .getActiveSpreadsheet()
    .copy("new_copy")
  const sheet = ss.getSheets()[0]
  const numRows = 10;
  sheet
    .getRange(1, numRows + 1, sheet.getLastRow(), sheet.getLastColumn())
    .clear()
  const URL = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export?format=xlsx`;
  SpreadsheetApp.getUi()
    .showDialog(
      HtmlService
        .createHtmlOutput(`<a href="${URL}" target="_blank">Download</a>`)
    )
}
Documentation
user_142716330
Related