Create a script that will get data from a sheet, create content to add within a Doc. Make the Doc, update the sheet data with the doc details, and email out to the user from the sheet the doc location.
- Select a Spreadsheet sheet by Name – getSheetByName(‘data’)
- Get the data from the sheet
- Loop through the sheet data, invoke a function makerDoc() sending the row array values and the index value.
- Create a new function makerDoc(), set a value for the row using the index from the array. The Array is zero based and the first row contains headings so we add 2 to the value to get the actual row value.
- Create variables for the message within the doc, and the name of the doc which can be generated from data sent into the function.
- Create a doc and select the doc body object. DocumentApp.create(docName); doc.getBody();
- Using the body append a paragraph with the message contents. body.appendParagraph(bodyData);
- Get the newly created doc’s url. doc.getUrl();
- Create values to send an email, including email address, subject and message body. Send the email with MailApp. MailApp.sendEmail(email,subject,emailBody);
- Return the row value and the message for the spreadsheet cell back.
- Use the response object from makerDoc() to select a range in the sheet, then update and set a value from the response object message. sheet.getRange(val.row,5).setValue(val.message);