Google Apps Script Learn Coding Projects Exercises Resources

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.

How to Get Sheet Row Data with Google Apps Script 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  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.

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.

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.

1. Select a Spreadsheet sheet by Name - getSheetByName('data')

2. Get the data from the sheet

3. Loop through the sheet data, invoke a function makerDoc() sending the row array values and the index value.

4. 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.

5. Create variables for the message within the doc, and the name of the doc which can be generated from data sent into the function.

6. Create a doc and select the doc body object. DocumentApp.create(docName); doc.getBody();

7. Using the body append a paragraph with the message contents. body.appendParagraph(bodyData);

8. Get the newly created doc’s url. doc.getUrl();

9. Create values to send an email, including email address, subject and message body. Send the email with MailApp. MailApp.sendEmail(email,subject,emailBody);

10. Return the row value and the message for the spreadsheet cell back.

11. 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);

How to Create a PDF and Email from Sheet data using Google Apps Script code lesson

Generate a PDF document on the fly and send that document as an attachment to an email with all the data coming from a spreadsheet. Standalone script connecting to a spreadsheet for data and updating the selected spreadsheet.

1. Get the ID of your spreadsheet that contains the data.

2. Use the id and open the sheet by the id. SpreadsheetApp.openById(id);

3. Select the sheet in the spreadsheet with the data you want, get the values without the headings as an array of rows.

4. Loop through the rows of data, using the data from the Spreadsheet to create some HTML code.

5. Use the Apps Script Utilities to create a blob of the html content. Utilities.newBlob(html,MimeType.HTML);

6. Set a name for the blob. blob.setName(`${row[0]} ${row[1]}.pdf`);

7. Get the email address, subject to send the PDF to.

8. Using MailApp send an email to the user, with html for the body and subject. Using the attachments property in the email add the blob of PDF. attachments:[blob.getAs(MimeType.PDF)]

9. Update the spreadsheet row data with a value that the PDF was sent.

10. Check the inbox for the PDF and the email.

UI Menu Maker

Bound scripts can use a trigger like the special onOpen() function, which runs automatically whenever a file is opened by a user who has edit access. Using this with the ui menu maker will automatically add the menu item to the spreadsheet and allow anyone who has edit permissions access the functions.

How to add a custom UI menu to your spreadsheet.

Using the onOpen() method will add the UI menu buttons to the spreadsheet menu when the application opens. If you need additional functions to run within the sheet when it opens you can add all the functions within this one onOpen().

How to send a custom message to the UI alert.

Create a function that will send a message string to be output into the spreadsheet alert window. This can now be used to create a popup alert with a custom message within your script.

How to select the values of the selection range cells.

Create a function that will get the selected values and output them into the alert menu content. You can use the getActiveRange() method to select the range of values to be used. Use getValues() to return the array of cell contents in a nested array of rows nested into a main array.

How to search the selected range cells for a value and then determine which cell matched the value and update the found cell properties.

Create values in your spreadsheet that you want to check for, if found that the cell array row item is equal to this value you can get the cell range and update the cell properties.

1. Within the function select the values of the active range selection when the button is pressed and the function runs.

2. Loop through the contents of the data, as you loop through the rows using indexOf check to see if the value of any of the items in the row array is equal to the value we are searching for. In the example I use the value “Laurence” The cell value must be equal to the value we are looking for, if there are any extra characters it will not return an index value within the row array.

3. If the value is found, the indexOf will return the index value, if no match is found the response will be -1. We can use this in a condition to check if the returned value is -1 or not.

4. Using the getRow() and getColumn() methods from the selection range, you can find the starting cell for the selection. The ind of the row array, and the indexOf of the matching column value, can then be used to add to the starting cell coordinates and calculate the current cell coordinates in the spreadsheet.

5. Use the calculated values of the row and column for the match, and get it as a range. Once you have the range you can then update the cell properties like setBackground().

6. Update the background color to yellow of the found cell.

How to update the selection cell values to new values.

Getting the selected range value, the dimensions of the array will need to match to the new value array in order to be able to update the range with the new values.

The array method map() allows us to return updated values of the array into a response that can then be used to create a new array.

1. Select the active data cell values.

2. Create a holding array that will be used to add the updated value to, as well as a starting value to a counter that will be added to the cell values.

3. Loop the data values for each row. Using map update and returning a new array for the row values. Add this newly created array for the row values into the holder array that will then be used to update all the cell content.

4. Within the map method, check if the cell value contains a dot. If it does then break the content at the dot removing the values up to the dot. This is needed so that once the cell has the value and the dot placed, we can remove the previous value and add a new one to the cell contents. First determine if the cell value has a dot already, this can be done using the indexOf which will return the dot index value or -1 if no dot is found.

5. Using trim() method for strings you can remove surrounding whitespace form the cell content

6. Convert the cell content to a string using the toString() method. They need to be strings in order to use the string methods in Apps Script on the values.

7. Increment the counter value by one for each new cell that will be written. Add the counter value with a dot separating it from the existing content.

8. Once the holder array is created, it can now be used to update all the selected cell values using setValues().

9. You can also clear any existing formatting using the clearFormat() method.

How to create Custom Functions in Sheets with Google Apps Script code

Google Sheets comes with 100s of built in functions

You can also create your own custom functions using Google Apps Script. Custom functions are created using standard JavaScript syntax within Google Apps Script.
Custom functions never ask users to authorize access to personal data.

To use custom functions click on the cell, and just like any other custom function you can select it in the formula bar for within sheets. Start by typing = in the formula bar, followed by the function name and provide the required arguments for that function. This can be values from other cells in the sheet. The Loading… will display in the sheet and once complete it will return the value in the cell. If there is an error in the input format that isn’t expected in the function you will see the returned error in the cell.

When naming your custom functions in sheets, avoid the built-in function names, as well do not end the name with _ as this is reserved for private functions. The name of the function must be set as the function name in the Apps Script. Best practice is to capitalize the name although capitalization is not required, it does make it easier to read the functions in the code.

Custom functions can be selected and run from the formula bar, by selecting the cell you want to apply the function to and starting to type with the = equal sign then typing in the function name.

How to Create custom Number Functions

1. Create a function name which expects a numeric input value

2. Create the calculation of the data from the input, using the return to send the calculated results back into the cell.

How to Create a custom function that uses the string values and calculates the length of the string.

1. Create a function that requires two parameters, both string values

2. Add the strings together , return the result

3. Add to the returned result the string length value

How to Create a custom function to calculate sales tax

1. Create the function with a numeric parameter

2. Multiply the input value by the value for the tax and return the results.

How to Create a custom function to return Latitude and Longitude of a string value location name.

1. Create a custom function that takes in one string argument

2. Using the Maps object creates a new geocoder. Maps.newGeocoder();

1. Use the geocoder and send the string value from the function into it. If there are results, select the latitude and longitude from the returned object for location data.

2. Return the custom string with Lat and Long back to the function return.

How to create Web Apps with Apps Script

Web Apps with Apps Script

Web apps allow you to publish your script to the web, with a unique URL that others can then access and interact with. Best practice is to use a standalone script for web apps, although you can also create the same webapp within a bound script. For the web app to return results it uses the default functions within Apps Script of either a doGet(e) or doPost(e) function or both can be used on the same endpoint. The results will be returned depending on the method used to connect to the webapp. If you open the URL in a browser the method used is GET.

Using the HtmlService.createHtmlOutput() this will output the string content as html code into the web app.

The e argument represents an event parameter that contains information from the request URL parameters. For the Get method the parameters will be as below in the object response.

{"contextPath":"","parameter":{"id":"3"},"contentLength":-1,"parameters":{"id":["3"]},"queryString":"id=3"}

The value of parameter and parameters are both objects that will contain any value parameter. The parameters can be added to the URL using the ? and a key with a value separated by an equal sign. You can add additional parameters using the & symbol to the request URL.

Create a custom response with data coming from a Spreadsheet. Using the parameter of id, retrieve the corresponding row value for the id value.

1. Using doGet(e) retrieve the e parameters and run the code in the default function for the webapp

2. Using a condition check if the property value of id is contained in the e parameters object. If it is then update the output html with the content from the spreadsheet row.

3. Select the sheet with the data that you want to use. SpreadsheetApp.openById(id).getSheetByName()

4. Using the getValues() retrieve the data in a nested array format. The row value can now be used to retrieve an item from the array, as each array nested within it represents a row of data from the spreadsheet. Return the data of the resulting row.

5. Update the output html with the sheet data

6. Create the return of the HTMLService and create the HTML output.

Do more with Web Apps

With web apps you can select the output content type.

Content Service - is ideal for outputting straight text content or MIME type content like JSON data. Content Service does not wrap the container with the iframe object like the HTML service does. Depending on how you want to access the data and the type of data Content Service vs the HTMLService will both output web app results.

Using the content service output JSON data into the web app.

1. Create an object in your app script

2. Using JSON.stringify(myObj) convert the object data into a string value

3. Set the Mimetype for the returned results. .setMimeType(ContentService.MimeType.JSON);

4. Return the object within the ContentService as text output. ContentService.createTextOutput()

With Apps Script you can create HTML as a string value and send it to the webapp as output.

Within the HtmlService you can also create html code from a file, the file is in the Apps Script menu under files. To create a new file, press the + in the files menu and select HTML file. Give the file a name, you don’t need to include the .html extension.

Using the HtmlService you can select the file and generate the output of the file as HTML.

How to use templated HTML

Render HTMl results from server side Apps Script code. Using the Scriptlets. To use scriptlets, use the syntax <?= ... ?> this will output the results of their code into the client side html page.

Using the scriptlet tags output the value of the variable from Google Apps Script.

1. Create a variable with the name val.

2. Create the output object from the template html code. const output = HtmlService.createTemplate(html);

3. Create some html code, you can also create code within the script tags as this will be evaluated and rendered into the web app page. Using the scriptlet syntax output the value of the variable into the client side code. Assign a value to the variable val into the output object.

4. Using the variable from the Apps Script you can hard code it directly within the HTML output that is being created, as this is expecting a string value.

5. Return the template output using evaluate() to construct the output.

How to use Apps Script code and functions directly into the client side code.

Within the web app output, you can use the scriptlets to get output object values from the server side. Assign the values to the output object in order to be able to retrieve them within the client side code. You can also invoke functions from the client side to return data within the Google Apps Script server side code.

1. Create an html file called index2 Within the file use the scriptlet to assign a value to data variable from the response data coming from a function on google script side called fromSheet().

2. Within the Apps Script code, create a function called fromSheet() which connects to a spreadsheet, gets the values from the sheet data and returns the array of data.

3. Back in the client side index2 file, using for each loop through the returned data from the fromSheet() function.

4. Output a value from the output object directly in the template using <?= ?>

5. Using the scriptlet add several numbers together to produce an output value. <?= 5 + 5 + 510 ?>

6. Using a condition with a value that comes from the output object, apply the different output results depending on the condition.

7. Send an object into the output data, loop through the array of data outputting the results into the client side page within the scriptlet.

How to create an Apps Script Web API Endpoint for AJAX connections output Sheet as JSON

doPost and doGet as an endpoint for web applications.

You can setup your webapp to serve as an endpoint for web applications. In this example we explore how to make both GET method requests and POST method requests to the endpoint, retrieving and returning data from a spreadsheet into the JavaScript code as data from an AJAX request.

In this lesson I use Visual Studio Code as my editor.

Also I use LiveServer addon for Visual Studio Code to output the webpage as http locally.

1. Create an HTML file that has an input field, a button and an element that can be used to output HTML into from the JavaScript code.

2. Create the doGet() function in yoru Apps Script code. Connect to a spreadsheet with some data that you want to use. Get all the values from the spreadsheet and build an object that can be returned from the sheet data and output into the web app as JSON data.

3. Use the ContentService to output the webapp content as JSON data, which then can be used by the JavaScript application to connect to the endpoint. return ContentService.createTextOutput(JSON.stringify(holder)).setMimeType(ContentService.MimeType.JSON);

4. Deploy the web app, and get the exec URL form the deployment to use in your JavaScript Code.

5. Create a JavaScript file that uses the webapp Exec URL with fetch. Select the DOM page elements as object, attach an eventlistener to the button on the page.

6. When the button is clicked invoke the function that will make the fetch request to the web app endpoint URL, retrieve the JSON data and output it to the page using JavaScript.

How to use a Web App and make a POST request with JavaScript fetch method to get JSON data back to a web application.

In this example we will connect to a web app URL endpoint with AJAX, sending a value from the input field for the row of data that will be retrieved from the endpoint. This row value can be changed within the input field selecting values from different selected rows coming from a spreadsheet.

1. Create the POST method in Apps Script to output the values of the spreadsheet data. Select from the e parameters the value of the row to return.

2. If there is a value for row, use this value to get the response back for the spreadsheet matching row data. To debug you can return the entire e object as a stringified value so that you can see the response in the web application. This content can be difficult to debug since the exec needs to be redeployed on all changes and the dev cannot be used to see the response values.

3. Update the JavaScript code to get the input value from the input field. Add a UL list to the output element that can then be used to add list items into. With JavaScript you can add page elements using the document.createElement() method. To append them to other existing element use the append() or appendChild() methods.

4. Create a new FormData object in JavaScript const formData = new FormData();

5. Add to the formData object the values of the input field under a property name row. formData.append('row',myInput.value);

6. Send the fetch request using the POST method and attach the body contents from the formData object. fetch(url,{ method:'POST', body: formData })

7. Add the response value from the AJAX request to the webpage as a new list item document.createElement('li')

Apps Script Web app Data transfer from ClientSide to ServerSide WebApp Sheet data and more

ClientSide to ServerSide WebApp

Google web apps can run client side code that can easily be used to connect to server side Apps Script functionality. To execute server-side functions from client-side code, use google.script.run. google.script.run is an asynchronous client-side JavaScript API available in web app HTML-service pages that can call server-side Apps Script functions.

In this example we will demonstrate how to send data objects from the Apps Script server side into the client side and use the data object within JavaScript. Also how we can send data from the client side input field values to the server side script to then be used to update and append content into a selected spreadsheet.

1. Setup the doGet() method to create the web app output page. Create an object with some data and add it to the html object that is created from the template file index HtmlService.createTemplateFromFile('index')

2. Create the template file index in the file menu of the Google Apps Script editor.

3. Add the data object from the server side using a scriptlet adding it into a Javascript variable called data. const data = <?!= JSON.stringify(data) ?>;

4. Create page elements, and select those with JavaScript. Create 2 input fields, and a button that can be used to invoke the function call sending data to the server side function. Add an event listener to the button

5. When the button is pressed, gather the input field data and create an object that will be the argument of the google script function sending to the function on the server side.

6. Using the google script service run a function in the Apps Script called testFun sending the data from the frontend to it. google.script.run.withSuccessHandler(onSuccess).testFun(temp);

7. Create a function that will receive the response data on the client side,the function that was used in the withSuccessHandler argument. Add an update to the page.

8. Get the input field values and create a function called testFun() on the server side within the google Apps Script. Connect to a spreadsheet and append the row of data to the sheet. Get the value of the last row and return that to the client side within the return response value.

Google Apps Script Triggers Simple Triggers automation and more

Google Apps Script Triggers for Automation

Triggers allow you to run a function automatically within Apps Script. There are simple triggers that are built in and use custom functions in order to start and invoke the function. Within the event object of each event, the triggered function contains an event object that has information about the context in which the event occurred. Some commonly used simple triggers are onOpen() which runs when the spreadsheet, document, is opened. There is onEdit() that runs when the user changes a value in a spreadsheet. Also for web apps doGet() and doPost() are simple triggers that get invoked when the user visits the web application on either method.

Simple triggers must be in a bound script in order to run the application triggers, they also fire automatically without asking for user permissions. They will not run if the file is opened in read only mode. They are also limited in some services that would require authorization. They cannot run for longer than 30 seconds. Simple triggers are subject to Apps Script trigger quota limits.

Installable triggers provide more flexibility for users, they let Apps Script run a function automatically and can also run services that require authorization. Just like the simple triggers the event object is included in each event, that contains information about the context of the event. Installable triggers always run under the account of the person who created them. Installable triggers are subject to Apps Script trigger quota limits.

You can create installable triggers with Apps Script code.

Create a function that you can add to run automatically with a time based trigger.

1. Create a function that opens a spreadsheet, and generates a random value that can get added to the sheet.

2. Create the trigger either manually or with code to run every 1 minute.

3. Close the application and reopen after several minutes. Confirm the function ran and updated your Spreadsheet.

4. Open the Triggers menu option, and delete the trigger.

How to use Document Service ​​with Apps Script DocumentApp Class and methods

The Document Service ​​allows scripts to create, access, and modify Google Docs files. DocumentApp is the parent class for the Documents, which contains properties that can be used to update the styling of elements, methods to create and open the documents. Once you have created a Document you can select the Document as an object and use the Classes to interact with it.  You can create or select an existing document or with a bound script get the active document.  The body is an element representing a document body. First get the body so you can use the contents of the document and interact with it.

How to setup Form Submission to Sheet spreadsheet data to get responses.

How to send an email auto response on form submission, setup custom logging into a sheet of data.   Go to Drive and select to create a new form. Within the form, setup questions to collect an email, and first and last name from the user. In the setting tab of the form you can customize the setting options. Select the response tab, and connect the form to a new spreadsheet by pressing the 3 dots icon. This will create a new spreadsheet where we will write the Apps Script code. In the new spreadsheet select under the extensions tab to open a new Apps Script which will be a bound script to the spreadsheet container.   To setup a trigger use the ScriptApp service and add a new trigger to run a function called added on the form submitted into the sheet.

Create Forms with Google Apps Script Code Forms Service Creating form

How to create forms with Google Form Service  Forms service allows scripts to create, access, and modify Google Forms.  Forms can be created with code, you can set the parameters, questions and set the type of questions all within the form service using Google Apps Script.   Create a form from scratch, send the form link to an email address. Forms will automatically collect results of the responses directly within the main form file, this can be accessed from your drive once the form is created.

How to use Spreadsheet Service SpreadsheetApp with Google Apps Script

SpreadsheetApp Class and Sheets Service Code examples  The Spreadsheet service allows scripts to create, access, and modify Google Sheets files. You can use sheets to store and hold data for other Apps Script applications. Sheets can serve as a database for data as the content is presented in a table format with rows and columns of cells each containing data.  How to create new sheets checking to see if the sheet exists or not.  Using the spreadsheetApp class you can select all the sheets contained in a spreadsheet. Select and create a sheet if it does not exist. Sheet names must be unique, if you try to create a sheet name within the spreadsheet that already exists you will see an error.  How to select sheet cells and update content within those cells.  Selecting the range can then use the range to get values from cells as well as set values within cells. Use the range methods to make a selection of cells that you want to update. There are several ways to select the range, be careful that the range dimensions match the content dimension for the array so that data can be added into the selected range.  How to add content and create additional content for columns of data.  You can append rows of content, setting the content within an array in Apps Script. Using the LanguageApp you can select to translate strings into other languages. How to remove empty sheets from your spreadsheet  Using getDataRange() you can select the entire available set of data within a sheet. This can then be used to get the values, returning all the values within that selected sheet.

Apps Script How to use SpreadsheetApp update Sheets data in Spreadsheet

How to Update Sheets and Spreadsheet content  and prepend rows to sheets.

There are many methods that can be used within sheets, we can also use JavaScript methods to do a lot of adjustments when creating code. 

How to create a new sheet and get the id to use in the sheet selection.

Once your sheet is created, you might want to reorder the sheets, create a sheet with a specific name and use that sheet. You can check to see if the sheet exists, add headings if they do not exist. Also using JavaScript methods and custom JavaScript functions that can help check and create content for your sheets.

1. Select the spreadsheet to use

2. Use a specific sheet by name, check to see if it exists and if it does not create it.

3. Set a sheet as active so that you can reorganize the sheets in the tab menu. You can select all the sheets and then check to see the sheet order, this is array based which means that the index value of the first sheet will be 0. By selecting the sheet names you can check to see if they match, if not you can move the sheet within the Spreadsheet class using the moveActiveSheet(), which can only be done with the sheet you intend to move being the active sheet.

4. Check if the first sheet matches the sheet order that you want.

5. Adding headings to the sheet, create an array of heading values that you want to use. Get the values of the headings array, and select the first row of content from the sheet. Both will be arrays, there are a number of ways to check if the contents are the same, one way is to convert the arrays into strings and check to see if the string values match. This can be done with JSON.stringify. If the values don’t match you can use the range of the first row and set the values to the array.

6. To capitalize content in JavaScript there isn’t a built-in method, but you can create one by selecting the content, converting it to lowercase, then selecting the first letter using charAt(0) and converting that to uppercase with toUpperCase(). Then join the string values together with a slice of the string value at position 1 which will remove the first letter from the string value.

7. Sheet Object can append rows to the end of the sheet data, to add the content at the top of the sheet you can use the insert methods. There is a method to insertRowsBefore(2,1) which will allow you to select the row that you want to do the insert and the number of rows you want to insert. To get the range you can select the range and chain the methods together to have a new object that contains the newly inserted rows.

8. Once you have the range that you want to insert, they are blank and you can update the contents of the cells using the setValues() which will write the values with the nested array data. Each row is an array nested within the main array object.

9. You can also insert blocks of nested arrays, by selecting the range and then using the insertRowsBefore() setting a value that matches the dimensions of the object you want to insert. Number of rows and columns for the data to be inserted.

10. In the example we create a function that will generate a random number from 1 to 1000 which can be used to quickly populate values in testing and create the values into the array blocks needed to match the content range.

Coding using Spreadsheet range selections and updates of Cells with AppsScript.  Spreadsheet range selections and updates of Cells.  Selecting the range allows you to update the cells within that selection. A range can be selected as a single cell or a collection of cells. Ranges provide access and can allow you to modify spreadsheet ranges. Ranges can be a single cell in the sheet or a group of adjacent cells within a sheet.  How to select ranges and how to select all content into a default range. Adding a block of formulas to a range of multiple cells.  How to select and update the entire range contents of all the cells within the range.

How to select ranges and how to select all content into a default range.

1. Select all the available cells that have data, this will return a block of cells with the dimensions of the last row and the last column of content. Even if cells have no content the getDataRange() returns all the full blocks with the furthest most cells of data.

2. Select a range just from the headings row you can also select this from the full data range object once you have the values in a nested array, using regular array methods you can then manipulate the array data. allData.getValues().slice(1);

3. Select the range you want to add borders too, getRange(2,5,sheet.getLastRow()-1,1) apply a solid border.

Adding a block of formulas to a range of multiple cells.

1. Select the sheet you want to use. Calculate the number of rows using the getLastRow() value and subtracting 1 so that you can account for the heading row.

2. Create an array to use to add the formulas into. Loop through all the rows that you want to create, update the values of the string formula, adjusting for each new row to get the content. Add the new formula in an array with a string contained in the array. Even if it's just the one column the value needs to be placed within its own array to set the proper structure for the range.

3. Set the formulas for all the cells in the range, the array with the formulas must match the dimensions of the range cells.

How to select and update the entire range contents of all the cells within the range.

1. ​​Create a function to use that will generate a random hex color. You can use a typical JavaScript function for this.

2. Select the sheet and the range you want to interact with. Using the setBackground() or setFontColor() apply a random color to all the cells within the range. To apply different values you can create an array with the same dimensions as the range and apply the array of values into the colors of the range.

3. Get all of the values in the sheet and randomize them with randomize();

4. Create a separate sheet, add some values to that sheet. Select the values, randomize() them and removeDuplicates()

5. Merge all the cells from the selected range. Once merged the cell range won’t randomize anymore since it will only have the one selected value because of the merge.

How to add Conditional Rules Format with Google Apps Script code.  Conditional Rules Format  Conditional rules can be selected, added and updated with Google Apps Script. These are rules that can be applied to content to add visual cell properties to the content. Each conditional format rule may contain a single boolean condition.  Class BooleanCondition can access boolean conditions in ConditionalFormatRules. These are criteria that can be used to evaluate the cell content, and if the value condition equates to true the rule is applied.  More info on Spreadsheet objects.  How to add a conditional rule to a range within your sheet.    How to see the rules and return values contained within the rules condition.   How to clear rules and add new rules    How to update a Conditional Format Rule

How to add a conditional rule to a range within your sheet.

1. Get all the rules for the sheet object getConditionalFormatRules();

2. Create a rule with conditions using newConditionalFormatRule()

3. Add the new rule into the rules array.

4. Set the conditional rules for the sheet with sheet.setConditionalFormatRules(rules);

How to see the rules and return values contained within the rules condition.

1. Select the sheet and get all the rules using the sheet.getConditionalFormatRules();

2. Loop through the rules array and get the values

How to clear rules and add new rules

1. Select all the rules from the sheet. To remove all existing rules use sheet.clearConditionalFormatRules();

2. Create a new SpreadsheetApp.newConditionalFormatRule() that can be applied to the sheet rules.

3. Add the new rule to the rules array, then reapply the rules to the sheet. sheet.setConditionalFormatRules(rules);

How to update a Conditional Format Rule

1. Create a new rule with newConditionalFormatRule()

2. Select the rules array with sheet.getConditionalFormatRules() from the sheet object

3. Using the index value, update the value of the rule object in the array.

4. Set all the rules to the sheet rules object, updating the existing rules with the new object data as a whole. sheet.setConditionalFormatRules(rules)

Apps Script how to use CalendarApp Class Methods and Calendars in Workspace. CalendarApp Methods for Google Calendar.  CalendarApp allows Apps Script to read and update Google Calendar. Using the Calendar service you will have access to the user's Google Calendar in addition it can also access additional calendars that the user has permissions for. CalendarApp class can be used to interact with the calendar object update, edit and delete calendar entries and events.  You can add and have multiple calendars within the calendar service for your account. All accounts come with a default calendar which is the email address and name of the user for the Google Account.  How to view all calendars for your account.  How to get events within a date range from the CalendarApp class.  How to select and get event information from a set day.  How to create an event and send invites to guests when creating the event.  The event creation method has advanced options that allow you to send invites, add guests, set a location and description for the event. Select your calendar object.  Set a start and end date, including time if you are not planning an all day event.  The time will adjust depending on the calendar timezone, setting with UTC time will set the event for that timezone and will show in the guests calendars within their own timezone.  Add the options options, like description, location, guests, and whether to send the guests invites.  Invites can only be sent at the time of creating the event.   The description and location can be updated. How to select an existing event by ID and how to get the ID of an event.  Once an event is created you can also update the event, below are some common updates to an event object.

How to view all calendars for your account.

1. Using the CalendarApp select all calendars this will return an array of calendar objects. CalendarApp.getAllCalendars();

How to get events within a date range from the CalendarApp class.

1. Select the calendar you want to use, you can use the default if you do not have the id of the calendar. CalendarApp.getDefaultCalendar();

2. Create search start and end data using the Date() constructor.

3. Set some options for the events response, you can limit the number of responses and apply a search string value to help narrow search results. Options can be used in object format.

4. Using getEvents return matching event objects cal.getEvents(start, end, opt);

5. Loop through the event objects array and get event information, output it into the Logger.

6. Get the guest list from the event, loop through the guests array and get the guest email and status. event.getGuestList();

How to select and get event information from a set day.

This code snippet will demonstrate how to send out an email to all guests whose status is still invited meaning they have not responded to the guest invite. Invites can only be sent at the time of event creation, all guests added afterwards will get the default of not being sent an invite when added to an event. To send out an email to let a guest know about the event, this can be done using the MailApp service and sending an individual email to them.

How to create an event and send invites to guests when creating the event.

The event creation method has advanced options that allow you to send invites, add guests, set a location and description for the event.

Select your calendar object

Set a start and end date, including time if you are not planning an all day event.  The time will adjust depending on the calendar timezone, setting with UTC time will set the event for that timezone and will show in the guests calendars within their own timezone.

Add the options options, like description, location, guests, and whether to send the guests invites.  Invites can only be sent at the time of creating the event.   The description and location can be updated.

Create the event using createEvent()  createEvent('New Event 2', start, end, opts)  Get the ID of the event to use to select the event in the next function.

How to select an existing event by ID and how to get the ID of an event.

Once an event is created you can also update the event, below are some common updates to an event object.

1. Select the event, using the day of the event you can list all of the events on that day. getEventsForDay(day);

2. Loop through the event objects and get the titles and ids. Output them into the Logger. Select the id of the event you want to select and update.

3. Get the event by id

4. Set the description of the event event.setDescription('New updated');

5. Set the title of an event event.setTitle('Another Title');

6. Set the color of the event, please note this will show in the color value and in your calendar. event.setColor("8");

7. Add more guests to the event, please note that no invite email will go out. event.addGuest()

How to create Apps Script code to access Drive Service DriveApp functions.   Apps Script Drive Service DriveApp. Drive service will let scripts create, find, and modify files and folders within the users Google Drive.  The DriveApp class allows scripts to interact with files and folders by allowing the script to create, find, and modify files and folders within Google Drive.  MimeType is an Enum (enumeration) that provides access to MIME-type declarations without typing the strings explicitly.  How to create a folder using DriveApp Class  Set the folder name in the parameters, to add the folder to the root folder of your drive you can use the DriveApp class directly, and if no folder is specified the files and folders get created directly in the main drive folder.  How to select a folder and add new folders into it.  Select the folder, this is most commonly done using the id of the target folder.  To create a folder you need to specify the name of the folder in the createFolder method.  If you want to add it to a specific location, select the folder object using DriveApp class first.  Add editors to the folder using addEditor().  How to loop through a folderIterator object.  You can select the starting point for the folder selection within the folder object, which can be selected using the folder id value.   Select all the editors of the folder, this will return an array object which can use typical array methods including the forEach() to loop through the contents.  Get the email of the editor from the DriveUser object.  Get all the folders from a selected folder.  This returns a folder Iterator object. Using the while loop check if the hasNext() returns true, if it does select the next folder in the list using the next() method.  Output the folder names into the Logger execution log output.

Create Move Updates Files and Folders within your Google Drive with Code.  How to search for a file with a value that is contained within the file name.  If you know the location of the files and want to retrieve files only that have a file name with a given value as part of the name, you can select all the files and then using the string method check if the file names contain the string value.  How to Search files using string methods.  Select the folder you want to search.   Return all the files within that folder. Loop through the files and check the filename which will be returned as a string value.  Since it's a string you can use string methods to check if the string includes a value.  Apply a condition depending on the results: update the file with a star or move it to trashed.  How to make copies of files and move files within the drive.  Select the folder you want to search. Get the files as file objects so that you can rename and move the files. Using makeCopy() create a copy of the file, setting a new name and moving it to a target folder.  Move the selected file into a folder by its id location.   ​​file.moveTo(moveFolder). How to update the contents of a file, and change the name.  Select the file folder you want to use to update the containing files.  Add a viewer to the file. Set the name of the file with the extension .html. Set the content of the file as HTML structured content file.setContent(html); Go to the drive location of the file, download it and open the downloaded file in your browser as an html file.  How to get the id of a file.  Select the folder with the files, iterate through the file in order to return all the file ids.  Each file will have a unique id value.  For html and text files you cannot open them directly in the drive so the id is not in the browser as it is with other file types and folders. How to get the file editors array . Having the id of the file you can now directly select the file object.  If you have the name of the file you can search for the file to return it as an object. Return the array of file editors into the Logger.  How to select an html file or text file and turn it into a PDF that gets emailed.  Select the file object, then convert the file into a blob.  Not all file types can be converted easily. Get the email of the active user, or set a value for the email.  Create some html content. Using the MailApp service send an html email, add an attachment with the file as a blob and set it as a mime type of PDF.  How to create and add Sheets and Docs files into a drive.  Using the DocumentApp class, create a file.  Get the id of the file.  Once you have the file as a file object you can then use the moveTo to move the file into another folder by selecting the folder as a folder object.  Using the SpreadsheetApp class, create a sheet file.  Select the id of the file, and get it as a file object using the DriveApp class.  Using the moveTo() and set the argument to the value of the destination folder object.

Sending Emails Messages Thread updates with GmailApp Gmail Service Functions.  Gmail Service GmailApp Class  Gmail Service GmailApp Class. Gmail Service allows the Apps Script to send email, create emails,  manage labels, select messages and threads in addition to other Gmail activities.  The difference between GmailApp and the Mail Service is that the MailApp is a simpler class that only allows the sending of email.   If you only want to send an email use the MailApp class, if you want to interact with content in your Gmail account select the GmailApp.  How to get the Gmail inbox threads.Using the GmailApp.getInboxThreads() get all the threads in the inbox .  You can see a count of threads in the inbox GmailApp.getInboxUnreadCount().Using the Thread Object array loop through all the threads and get the messages within each thread.  Create a message array and loop through all the messages getting the subject and id and listing them into the log.  How to reply to a message and get message info from the GmailMessage object Select the message object by its id Using forward - send the message forwarding it to a new email. Get the message details and create a string value that contains all the message details. Using reply send all the body contents back in a reply to the message.  How to update message object, send it to trash. Select the message by its id Get the thread value thread = message.getThread(); Get all the messages in a thread object. Using moveToTrash() move the message object to trash.How to add labels to threads. create a label to add to your thread.  GmailApp.createLabel('A New Label') Select the thread to apply the label to.   You can also select all the account labls and loop through them as an array Add the thread to the label label.addToThread(thread) Move the thread to the inbox thread.moveToInbox()  How to add email attachments to your drive with Apps Script.  Select the thread object that contains messages you want to check for attachments.  Select the messages from the thread, and loop through the messages. Get the attachments from the message, loop through the attachments.  Create blobs from the attachments and then create a file with the blob object.

Explore Slides Service with SlidesApp Class with Apps Script code.  Slides Service with SlidesApp Class  Slides Service within Apps Script allows you to create, access, and modify Google Slides files. The SlidesApp Class can create, edit , update and open Slides Presentations.  Create a slide presentation to use for the following exercises  Add text and insert a shape with text into a new slide  Explore Slides Service with SlidesApp Class with Apps Script code.  How to generate images from all the slides in your presentation and save the images to your drive.  Select the presentation and append a new slide to it.

Using the slide object add new text with insertText()

Add a new shape insert the shape adding a text box with text.

Add more text into the shape.How to add a web image to your presentation.

Select the presentation.

Add a new slide with appendSlide()

Insert the image using the url of the image.

Set a background by selecting the slide background and then setting a hex value for the fill color.  How to add layouts selecting layouts from the presentation.

Select the presentation, and append a new slide.

Select from the layouts array a layout to use for the new slide.

Set a background color of the slide.

Get all the shapes in the slide and add text to them.  How to set random background colors to your slides.

Select the presentation and all the slides in an array

Loop through all the slides, add a new textbox within a shape.

Generate a random hex value for a color.  Select the slide background and then set the fill color of the slide background.

How to generate images from all the slides in your presentation and save the images to your drive.

In order to access the URLs of your presentation you will need to get the authentication token.  Since this exercise requires using the UrlFetchApp to make a fetch request to return the web contents from a URL, it will need to authenticate the fetch request first.  Without authentication the response from the page will be the text message that this request is not authenticated.  You can connect to your Google Workspace files with the GET method, and send the authorization headers as in the para below.

Below is an example of what the slide presentation and object id will return as a string JSON value from the URL.