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);
}
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?
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…