Extending Google Apps-Spreadsheet using Google Apps Script

Google Apps Script is a JavaScript cloud scripting language to automate tasks. I gave a session on #GDGAhmedabad #DevFestAhm with 4 live demo of the application.

Transcript

1. Presented at GDG DevFest Ahmedabad 2015 Presented By : Dipali Vyas Project Manager @ Zestard Technologies Date: 27-Dec-2015 Google Apps Script
2. What is GAS - Google Apps Script? And What Magic we are going to see. Google Apps Script is a JavaScript cloud scripting language to automate tasks ▸ Editor in a browser & managed in cloud (no install/setup, files stored,shared, versioned) ▸ JavaScript runtime in the cloud ( compliant, debuggable, and performant ) ▸ Set of APIs and hooks to Google products (extend and enhance, built in security, distribution mechanisms 2 11 Google apps, 1 platform in the cloud
3. Getting Started with Google Apps Script (GAS) ▸ Standalone script https://script.google. com Google Drive > Create > More > Script ** ▸ Spreadsheet/App-bound script Tools >Script Editor ** ▸ Sites-bound script More > Manage Site > Apps Scripts > Add new script ** ** Do take care of the Authorization Step while scripting 3
4. Standalone Script Example function createAndSendDocument() { // Create a new Google Doc named 'DevFestAhm15 GAS Demo1' var doc = DocumentApp.create('DevFestAhm15 GAS Demo1'); // Access the body of the document, then add a paragraph. doc.getBody().appendParagraph('This document was created by Google Apps Script-Standalone Script. This is part of DevFestAhm15 GAS Demo1 provided by Dipali Vyas during the presentation in front of excellent participants of the session '); // Get the URL of the document. var url = doc.getUrl(); // Get the email address of the active user - that's you. var email = Session.getActiveUser().getEmail(); // Get the name of the document to use as an email subject line. var subject = doc.getName(); // Append a new string to the "url" variable to use as an email body. var body = 'Link to your doc created at DevFestAhm15 : ' + url; // Send yourself an email with a link to the document. GmailApp.sendEmail(email, subject, body); } ==========End 4
5. Spreadsheet / App Bound Script Example Bound scripts can call a few methods : ▸ getActiveSpreadsheet(), getActiveDocument(), and getActiveForm() allow bound scripts to refer to their parent file without referring to the file's ID. ▸ getUi lets bound scripts access the user interface for their parent file to add custom menus, dialogs, and sidebars. ▸ In Google Sheets, getActiveSheet(), getActiveRange(), and getActiveCell() let the script determine the user's current sheet, selected range of cells, or selected individual cell.setActiveSheet(sheet) and setActiveRange(range) let the script change those selections. ▸ In Google Docs, getCursor() and getSelection() let the script determine the position of the user's cursor or selected text. setCursor(position) and setSelection(range) let the script change those locations. 5
6. Google Apps Script’s Fundamentals 6 Integration Triggers Custom UI Custom UI ▸ Menus ▸ Sub-Menus ▸ Dialogs ▸ SideBars Triggers ▸ Simple triggers (built-in functions) ▸ Installable triggers Integration With all Google apps, we can initially check
7. Triggers in Apps Script A trigger is a type of script resource that listens for a particular event and executes a function when that event fires. ▸ Simple triggers ▸ Specially-named built-in functions that are specific to spreadsheets ▹ onEdit ▹ onOpen ▹ onInstall ▸ Installable triggers ▸ Time-based triggers ▸ Container-bound installable triggers (let's make one!) 7
8. Use Cases App Reference #1 Grading made easy with Flubaroo ▸ Create quiz using Google Forms ▸ Automatically grades against answer key ▸ Can email results and answers to each student ▸ Provides charts to analyze the results 8
9. Use Cases App Reference #2 Many types of mail merge ▸ Define a simple template using a draft email, document, sites page, or spreadsheet cell ▸ Add placeholder values that should be dynamically replaced ▸ Each spreadsheet row merged with template and sent out (Example of MailMerge by Amit Agrawal) 9
10. Easy Reference for the Beginners ▸ http://developers.google.com/apps- script User guide covering basic functionality 30+ tutorials with code Videos ▸ YouTube channels ▸ GoogleDevelopers and GoogleApps Stack Overflow Tag questions with google-apps-script ) ▸ Very active community, Google moderated Google Apps Developer Blog 10
11. 11 THANKS! Any questions? Special thanks to all the people who made and released these awesome resources for free.
12. Google Apps Script Presented at GDG DevFest Ahmedabad 2015 Presented By : Dipali Vyas Project Manager @ Zestard Technologies Date: 27-Dec-2015 Email: dipalia@gmail.com Twitter: @dipalia