Google Sheets modules
In an Adobe Workfront Fusion scenario, you can automate workflows that use Google Sheets, as well as connect it to multiple third-party applications and services.
For instructions about connecting your Google Sheets account to Workfront Fusion, see Create a connection to Adobe Workfront Fusion - Basic instructions
Access requirements
You must have the following access to use the functionality in this article:
To find out what plan, license type, or access you have, contact your Workfront administrator.
For information on Adobe Workfront Fusion licenses, see Adobe Workfront Fusion licenses.
Prerequisites
To use Google Sheets modules, you must have a Google account.
Triggers
Watch Rows
Retrieves values from every newly added row in the spreadsheet.
The module retrieves only new rows that have not been filled in before. The trigger will not process an overwritten row.
Actions
Add a Row
This module appends adds a row to a sheet.
When you configure Google Sheets modules, Workfront Fusion displays the fields listed below. Along with these, additional Google Sheets fields might display, depending on factors such as your access level in the app or service. A bolded title in a module indicates a required field.
If you see the map button above a field or function, you can use it to set variables and functions for that field. For more information, see Map information from one module to another in Adobe Workfront Fusion.
Update a Row
This module allows you to change the cell content in a selected row.
Clear a Row
Deletes values from a specified row.
Delete a Row
Deletes a specified row.
Get a Cell
Retrieves a value from a selected cell.
Update a Cell
Clear a Cell
Deletes a value from a specified cell.
Add a Sheet
Creates a new sheet in a selected spreadsheet.
Create a Spreadsheet
Delete a Sheet
Deletes a specific sheet.
Make an API Call
This action module allows you to perform a custom API call.
Searches
Search Rows
Searches rows using the filter options.
Search Rows (Advanced)
Returns results matching the given criteria.
Get Range Values
List Sheets
This module returns a list of all sheets in a spreadsheet.
Usage Limits
If the error 429: RESOURCE_EXHAUSTED
occurs, you have exceeded the API rate limit.
The Google Sheets API has a limit of 500 requests per 100 seconds per project, and 100 requests per 100 seconds per user. Limits for reads and writes are tracked separately. There is no daily usage limit.
See more details at developers.google.com/sheets/api/limits.
Tips & Tricks
How to get Empty Cells from a Google Sheet
Use the Search Rows (Advanced) module & use this formula to get the columns which are empty.
select * where E is null
Here "E" is the column & "is null" is the condition. You can create a more advanced query using [Google Query Lang](https://developers.google.com/chart/interactive/docs/querylanguage).
Add a button in a sheet to run a scenario
-
In Workfront Fusion, insert the Webhook > Custom webhooks module/trigger in the scenario and configure it (see Webhooks).
-
Copy the webhook’s URL.
-
Execute the scenario.
-
In Google Sheets, choose Insert > Drawing… from the main menu bar.
-
In the Drawing window, Click the Text box icon near the top of the window.
-
Design a button and click the Save and Close button in the top-right corner:
-
The button will be placed in your worksheet. Click the three vertical dots in the button’s top-right corner:
-
Choose Assign script… from the menu.
-
Enter the name of your script (function), e.g.
runScenario
and click OK: -
Choose Tools > Script editor from the main menu bar.
-
Insert the following code:
-
The name of the function must correspond to the name you specified in step 9.
-
Replace the URL with the webhook’s URL you copied in step 2.
function runScenario() {
UrlFetchApp.fetch("");
}
-
-
Press Ctrl+S to save the script file, enter a project name and click OK.
-
Switch back to Google Sheets and click your new button.
-
Grant the required authorization to the script:
-
In Workfront Fusion, verify that the scenario has successfully executed.
Storing dates in a spreadsheet
If you store a Date value in a spreadsheet without any formatting, it will appear in the spreadsheet as text in ISO 8601 format. However, Google Sheets formulas or functions that work with dates that do not understand this text (Example: formula =A1+10
) will display the following error:
To help allow Google Sheets to understand the date, format it with the formatDate (date; format; [timezone]) function. The correct format passed to the function as the second argument depends on the spreadsheet’s locale settings.
To determine the correct format:
-
Choose File > Spreadsheet settings from the main menu to verify/set the locale.
-
Once you have verified/set the proper locale, determine the corresponding date and time format by choosing Format > Number from the main menu. The format is displayed next to the Date time menu item:
-
To compose the correct format that should be passed to the formatDate() function, refer to the list of Tokens for date and time formatting in Adobe Workfront Fusion.
Example: The use of MM/DD/YYYY HH:mm:ss
format for the United States locale:
Exploiting Google Sheets functions
If you miss a built-in function, but it is featured by Google Sheets, you may exploit it. For more information, see Use Google Sheets functions in Map items using functions in Adobe Workfront Fusion .
Keep Google Sheets from changing numbers into dates
You might find that a string of numbers that you are using as text is being interpreted as a date in a Google worksheet. For example, you type 1-2019, intending it as text, but Google interprets it as a date. You can pre-format the number as plain text to prevent this.
- In Google Sheets, highlight the column or cell containing the number or numbers.
- Click Format > Number > Plain text.
Another workaround in Workfront Fusion is to type an apostrophe (') before a number, for example, '1-2019 or '1/47. The apostrophe does not display in the cell after the data is sent from Workfront Fusion.