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

CodePudding user response：

Solution:

• When defining the source and target ranges, don't use getDataRange() (which includes all sheet data), but getRange(row, column, numRows, numColumns), where numColumns is 10 (corresponding to column J).
• After setting all the values, use getFormulas and setFormulas to set the formulas on your desired cells.

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