I've been struggling to build a specific weekly stock system reports. So to give you a basic overview, I have a mastersheet that I want to generate reports from, triggered by an UI button. The first step however is to create a folder for that week to place the PDF's in. I can create the folder, and I can generate the PDF in my root Google Drive folder, but I can't seem to move the PDF anywhere after that. I have attempted to use .moveTo() but I can't get that to work. Does anyone have any advise?
function onOpen(e)
{
SpreadsheetApp.getUi()
.createMenu('Physical')
.addItem('New folder','newFolder')
.addItem('Generate PDF','generatePDF')
.addToUi();
}
function newFolder(){
var today = new Date();
var week = Utilities.formatDate(today, "Europe/Amsterdam", "w"); //need to find a way to minus 1 for the current week
var spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId(); //time to create a new folder
var spreadsheetFile = DriveApp.getFileById(spreadsheetId);
var folderId = spreadsheetFile.getParents().next().getId();
var parFolder = DriveApp.getFolderById(folderId)
var destFolder = parFolder.createFolder('Week ' + week);
}
function generatePDF(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var speadsheetFile = ss.getId();
var file = DriveApp.getFileById(speadsheetFile);
var folderId = file.getParents().next().getId();
var pdf = DriveApp.createFile(ss.getBlob())
pdf.moveTo(folderId); //find way to move file either to destination folder or to parent folder
}
Description
These types of situations are hard to test because the circumstances are specific to the OP questioner. However, I believe this will work.
Using the PropertyService Script Properties, store the newly created folderId and then get that id from Script Properties to move the file.
A note of caution, I didn't check for the case if the week changes and a new folder is not created, the pdf will go to the previous week folder.
Regarding creating a button and linking a function to the button see this article Buttons in Google Sheets
Script
function newFolder(){
var today = new Date();
var week = Utilities.formatDate(today, "Europe/Amsterdam", "w"); //need to find a way to minus 1 for the current week
var spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId(); //time to create a new folder
var spreadsheetFile = DriveApp.getFileById(spreadsheetId);
var folderId = spreadsheetFile.getParents().next().getId();
var parFolder = DriveApp.getFolderById(folderId);
var folderName = 'Week '+week;
// check if folder already exists
var subFolders = parFolder.getFoldersByName(folderName);
var destFolder = null;
if( subFolders.hasNext() ) {
SpreadsheetApp.getUi().alert("Folder "+folderName+" already exists");
destFolder = subFolders.next();
}
else {
destFolder = parFolder.createFolder(folderName);
}
// store folder id to Script Properties
var props = PropertiesService.getScriptProperties();
props.setProperty("foldeId",destFolder.getId());
}
function generatePDF(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
// get folder id from Script Properties
var folderId = PropertiesService.getScriptProperties("folderId");
if( !folderId ) {
SpreadsheetApp.getUi().alert("Property folderId not found");
return;
}
var pdf = DriveApp.createFile(ss.getBlob())
pdf.moveTo(folderId); //find way to move file either to destination folder or to parent folder
}
Reference