Google Apps Script allows you to create custom blocks of code that do stuff, and there is a lot of stuff you can do with it. Container-bound Scripts – the Google file the Apps Script is attached to is known as the container. Bound scripts behave just like Standalone scripts but they have several special functions, they do not show up in drive and they cannot be detached from their container files. Script can be bound to Sheets, Docs, Slides, or Forms. To reopen the script in the future you can open the container file and select the script editor, or you can go directly to the https://script.google.com/ Apps Script home page, and select the project from there. For bound scripts it will show the container file icon, with a small Apps Script logo on it indicating that it is a bound script.
Send emails to users from a Google Spreadsheet using Google Apps Script.
- Get the spreadsheet object – because this is a bound script you can use SpreadsheetApp.getActiveSpreadsheet().
- Select the Sheet object getActiveSheet() which will return the sheet that is currently selected in the spreadsheet.
- Select the range for the data that you want to use, getDataRange() will select the entire contents of all the data in the sheet.
- Once you have the range of data cells you can now get the values into an array, each row will be a separate nested array within an array for the entire sheet. getValues()
- To remove the first row of data which is typically used for headings, slice(1) the values starting at index 1.
- Iterate through all the items in the main array, each item is a row of data from the sheet. data.forEach((row)=>{})
- Create the HTML message as a variable, the email as a variable, and the subject as a variable.
- Use MailApp to send the email using sendEmail method. *You will need to accept permissions for the app.