用于保存工作表中指定名称的文件的按钮 [英] Button that saves file with name specified in sheet

问题描述

我有一个带有按钮的工作表:

I have a sheet that has a button:

该按钮运行以下脚本:

This button runs this script:

function exportarPlanilha() {
  const exportSheetName = 'Demonstrativo';  // Please set the target sheet name.

  // 1. Copy the active Spreadsheet as a tempora Spreadsheet.
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet().copy('tmp');

  // 2. Convert the formulas to the texts.
  const targetRange = spreadsheet.getSheetByName(exportSheetName).getDataRange();
  targetRange.copyTo(targetRange, {contentsOnly:true});

  // 3. Delete the sheets except for a sheet you want to export.
  spreadsheet.getSheets().forEach(sheet => {
    if (exportSheetName != sheet.getName()) spreadsheet.deleteSheet(sheet)
  });

  // 4. Retrieve the blob from the export URL.
  const id = spreadsheet.getId();
  const xlsxBlob = UrlFetchApp.fetch(`https://docs.google.com/spreadsheets/export?id=${id}&exportFormat=xlsx`, {headers: {authorization: `Bearer ${ScriptApp.getOAuthToken()}`}}).getBlob();

  // 5. Crete the blob as a file.
  var folder = DriveApp.getFoldersByName("CLIENT_FOLDER").next();

  folder.createFile(xlsxBlob.setName(`${exportSheetName}.xlsx`));
  // DriveApp.createFile....
  // 6. Delete the temporate Spreadsheet.
  DriveApp.getFileById(id).setTrashed(true);
}

此脚本将工作表导出到我的Google云端硬盘中的文件夹(在脚本中假设指定为"CLIENT_FOLDER").但是，它将文件另存为"Demonstrativo.xlsx"("exportSheetName")，我需要它根据单元格B97中指定的名称保存文件(在此示例中为"this_should_be_the_filename"，但实际上它是一个将许多变量连接成字符串的公式).我该怎么办?

This script exports the sheet to a folder in my Google Drive (hypothetically specified in the script as "CLIENT_FOLDER"). However, it is saving the file as "Demonstrativo.xlsx" ('exportSheetName') and I need it to save the file according to the name specified in cell B97 (in this example it is "this_should_be_the_filename", but in reality it is a formula that concatenates many variables into a string). How could I do that?

推荐答案

首先获取单元格的值:

var filename = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B97").getValue()+".xslx";

folder.createFile(xlsxBlob.setName(filename));

这篇关于用于保存工作表中指定名称的文件的按钮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！