Learn Google Apps Script Introduction to Google Apps Script

by

Introduction to Google Apps Script Macros within Google Sheets can be used to write Apps Script Code. Create a new spreadsheet and add some data into the sheet. In the top menu under extensions select Macro. Google Apps Script is created using standard JavaScript Syntax. Apply some changes and update the text color of some cells within your Sheet. Then Select Save in the Macro recorder to end the recording of the macro. Give the macro you created a name and press save. Select the Apps Script menu item under the Extensions tab to open the Apps Script editor. This will open the editor, where you can see the created Apps Script code. Below code selects the range A1:A4 and activates it. “spreadsheet.getRange(‘A1:A4’).activate();“ Next using the active range it applies the font color of blue to the cell contents. “Spreadsheet.getActiveRangeList().setFontColor(‘blue’);“ You can now edit and update the Apps Script code. This will still run under the macro name every time that marco is selected. Next copy and paste the macro code. Create a new function with a new name. Go back into the Google Sheet, select Extensions then macro in the menu. There should be an option to import the macro. Select that and then import the new function as a macro into Google Sheets. Select macro under the menu again, you should see a new macro with the same name as your function, which you can now click and run.

Send emails to users from a Google Spreadsheet using Google Apps Script.

  1. Get the spreadsheet object – because this is a bound script you can use SpreadsheetApp.getActiveSpreadsheet().
  2. Select the Sheet object getActiveSheet() which will return the sheet that is currently selected in the spreadsheet.
  3. Select the range for the data that you want to use, getDataRange() will select the entire contents of all the data in the sheet.
  4. 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()
  5. To remove the first row of data which is typically used for headings, slice(1) the values starting at index 1.
  6. Iterate through all the items in the main array, each item is a row of data from the sheet. data.forEach((row)=>{})
  7. Create the HTML message as a variable, the email as a variable, and the subject as a variable.
  8. Use MailApp to send the email using sendEmail method. *You will need to accept permissions for the app

Leave a Comment