Reporting with Google
Link Google Account to Utility Cloud
- Select the Account tile from the Landing Page.
- Search for and select an Account.
- In the bottom right side of the screen, enter a valid Gmail address in the Email field and select Link.
Follow the Google instructions to allow Utility Cloud access to this Gmail address.
Security Rights Required: View Accounts; Edit Accounts
Quick Steps to Creating Google Sheets
Log into the Gmail account that is linked to Utility Cloud
- Go into the Google Drive and create a new google sheet. For more information on how to access the Google Drive please see Accessing and uploading to Google
- Create a tab and label it "Report". This will appear in a format of your choosing when a workflow is completed. This is a report that will be returned to Utility Cloud when the workflow is completed. This tab will have data linked to it from the Data tab or other sheet(s) with the workflow data.
- Create a tab and label it "Data". The data tab will be the sheet that the workflow data is sent to from Utility Cloud.
- Run a report (Completing a workflow will trigger a report to be run) from Utility Cloud that is linked to the Google Sheet; this will populate the data on the data sheet so that you will know which fields to use from the data sheet to populate into your form on the report sheet. See Workflow classes for more information on setting up the link between the workflow and the Google Sheets report.
Hints for linking between the data sheet and the report sheet
Link the data from the data sheet to the report sheet using formulas like HLookup
HLookup searches across the first row of a range for a key and returns the value of a specified cell in the column found. (https://support.google.com/docs/answer/3093375)
=IFERROR(HLOOKUP("uc_columnheader", Data!A1:EZ2, 2, False), "")
HLOOKUP(search_key, range, index, [is_sorted])
search_key - The value to search for. For example, 42, "Cats", or I24.
range - The range to consider for the search. The first row in the range is searched for the key specified in search_key.
index - The row index of the value to be returned, where the first row in range is numbered 1.
If the index is not between 1 and the number of rows in range, #VALUE! is returned.
is_sorted - [OPTIONAL - TRUE by default] - Indicates whether the row to be searched (the first row of the specified range) is sorted.
If is_sorted is TRUE or omitted, the nearest match (less than or equal to the search key) is returned. If all values in the search row are greater than the search key, #N/A is returned.
If is_sorted is set to TRUE or omitted, and the first row of the range is not in sorted order, an incorrect value might be returned.
If is_sorted is FALSE, only an exact match is returned. If there are multiple matching values, the content of the cell corresponding to the first value found is returned, and #N/A is returned if no such value is found.
Google Sheets Function Help
Google Sheets Function List: https://support.google.com/docs/table/25273?hl=en
QUERY Function: Returns multiple columns of data using a SQL statement (https://support.google.com/docs/answer/3093343?hl=en)
INDEX Function: Returns the content of a cell, specified by row and column offset. (https://support.google.com/docs/answer/3098242)
MATCH Function: Returns the relative position of an item in a range that matches a specified value. (https://support.google.com/docs/answer/3093378)
For more information on Accessing Google Drive see Accessing and uploading to Google or review the below document:
