Export a specific range Google Spreadsheet in Excel and keep formulas

user_14680755 0
Viewed 193

With this script I could export a spreadsheet in excel file.

Now I would to export only the spreadsheet from column A to column J and keep the formulas only in the cells: A3, C3, F3, G3, H3, F5, G5, H5.

All other cells of the excel file must be plain text.

How could I proceed?

function onOpen(e) {
  SpreadsheetApp.getUi()
      .createMenu('Custom')
      .addItem('Download as XLSX', 'downloadXLS_GUI')
      .addToUi();
}
function downloadXLS_GUI() {
  var sh = SpreadsheetApp.getActiveSheet();
  var nSheet = SpreadsheetApp.create(sh.getName()+": copy");
  
  var d = sh.getDataRange();
  nSheet.getSheets()[0].getRange(1,1,d.getLastRow(),d.getLastColumn()).setValues(d.getValues());
  
  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

Solution:

Code sample:

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');
}
user_106120110
Related