i hope someone can help me. So bascically the function has a trigger based on a google forms application. In the Forms i got a Dropdown Menu with 5 Names. These Names get always get put in Row 1 of the Google Sheet. Now the Script creates a Doc with the Data from the Sheet and puts it in a specific Folder (const destinantionFolder) - My Goal now is to change the destinationFolder based on the Name that is selcted from the Dropdown - so i got 5 different text variations in the Sheets Cell (The 5 Names) that should lead to 5 different destinationFolders. How do i define that? Huge thanks for helping me!
this is the code rightnow (i got it from a YT Video from Jeff Everhart):
//This value should be the id of your document template that we created in the last step
const googleDocTemplate = DriveApp.getFileById('112VRkj6msylp-vwaSmHqLNQDosCBsP0HaMxxxx');
//This value should be the id of the folder where you want your completed documents stored
const destinationFolder = DriveApp.getFolderById('1wI4QEZ6iC3Ur9CgTZtg4DmMfkxxxxx')
//Here we store the sheet as a variable
const sheet = SpreadsheetApp
.getActiveSpreadsheet()
.getSheetByName('Data')
//Now we get all of the values as a 2D array
const rows = sheet.getDataRange().getValues();
//Start processing each spreadsheet row
rows.forEach(function(row, index){
//Here we check if this row is the headers, if so we skip it
if (index === 0) return;
//Here we check if a document has already been generated by looking at 'Document Link', if so we skip
it
if (row[14]) return;
//Using the row data in a template literal, we make a copy of our template document in our
destinationFolder
const copy = googleDocTemplate.makeCopy(`${row[2]} - Infos` , destinationFolder)
//Once we have the copy, we then open it using the DocumentApp
const doc = DocumentApp.openById(copy.getId())
//All of the content lives in the body, so we get that for editing
const body = doc.getBody();
//In these lines, we replace our replacement tokens with values from our spreadsheet row
body.replaceText('{{Zeitstempel}}', row[0]);
body.replaceText('{{NAME}}', row[2]);
body.replaceText('{{Intro}}', row[5]);
body.replaceText('{{Ziel}}', row[6]);
body.replaceText('{{Abgehalten}}', row[7]);
body.replaceText('{{Kooperation}}', row[8]);
body.replaceText('{{FinanzZiel}}', row[9]);
body.replaceText('{{Invest}}', row[10]);
body.replaceText('{{Invest20}}', row[11]);
body.replaceText('{{Notizen}}', row[3]);
body.replaceText('{{Startzeit}}', row[13]);
//We make our changes permanent by saving and closing the document
doc.saveAndClose();
//Store the url of our new document in a variable
const url = doc.getUrl();
//Write that value back to the 'Document Link' column in the spreadsheet.
sheet.getRange(index + 1, 15).setValue(url)
})
}```
And thats the new new new Code:
function createNewGoogleDocs() {
//This value should be the id of your document template that we created in the last step
const googleDocTemplate = DriveApp.getFileById('TemplateID');
//This value should be the id of the folder where you want your completed documents stored
// Define folder id per user
const userFolder = {
Alina: "1ImNRXlyaFPGEatDMgi4cQ2JhPxxxxx",
Cem: "1xHhxupTXD8KRYkSk2Lll31pDcQxxxxx",
Constantin: "1wI4QEZ6iC3Ur9CgTZtg4DmMfkxxxxx",
Marie: "1mvZbp-CQP-oWsVeVv7Cc2htXExxxxx",
Johanna: "FolderIDxxxxx2",
};
//Here we store the sheet as a variable
const sheet = SpreadsheetApp
.getActiveSpreadsheet()
.getSheetByName('Data')
//Now we get all of the values as a 2D array
const rows = sheet.getDataRange().getValues();
//Logger.log(rows);
//Start processing each spreadsheet row
rows.forEach(function(row, index){
//Here we check if this row is the headers, if so we skip it
if (index === 0) return;
Logger.log(row[14]);
//Here we check if a document has already been generated by looking at 'Document Link', if so we skip
if (row[14]) return;
//Using the row data in a template literal, we make a copy of our template document in our
Logger.log(row[1]);
Logger.log(userFolder[row[1]]);
const destinationFolder = DriveApp.getFolderById(userFolder[row[1]]);
const copy = googleDocTemplate.makeCopy(`${row[2]} - Infos` , destinationFolder)
//Once we have the copy, we then open it using the DocumentApp
const doc = DocumentApp.openById(copy.getId())
//All of the content lives in the body, so we get that for editing
const body = doc.getBody();
//In these lines, we replace our replacement tokens with values from our spreadsheet row
body.replaceText('{{Zeitstempel}}', row[0]);
body.replaceText('{{NAME}}', row[2]);
body.replaceText('{{Intro}}', row[5]);
body.replaceText('{{Ziel}}', row[6]);
body.replaceText('{{Abgehalten}}', row[7]);
body.replaceText('{{Kooperation}}', row[8]);
body.replaceText('{{FinanzZiel}}', row[9]);
body.replaceText('{{Invest}}', row[10]);
body.replaceText('{{Invest20}}', row[11]);
body.replaceText('{{Notizen}}', row[3]);
body.replaceText('{{Startzeit}}', row[13]);
//We make our changes permanent by saving and closing the document
doc.saveAndClose();
//Store the url of our new document in a variable
const url = doc.getUrl();
//Write that value back to the 'Document Link' column in the spreadsheet.
sheet.getRange(index + 1, 15).setValue(url)
})
}
