이 콘텐츠는 선택한 언어로 제공되지 않습니다.
Chapter 8. Connecting to Google applications
An integration can connect to these Google applications: Gmail, Calendar, Sheets. See the following topics for details:
8.1. Registering Fuse Online as a Google client application
In an integration, to be able to connect to Gmail, Google Calendar, or Google Sheets, you must register your Fuse Online environment as a Google client application. During registration, you enable Google APIs and create credentials that Fuse Online uses to access the Google APIs that you have enabled.
With registration in place, you can create multiple connections to Gmail, Google Calendar, and Google Sheets. You can use each connection in multiple integrations. While each connection to a Google application can use the same Google client ID and Google client secret, which you obtain during registration, each connection can access a different Google account, which you choose.
This procedure instructs you to enable the Gmail API, the Google Calendar API, and the Google Sheets API. However, you can enable only the API(s) that you need rather than all three APIs. At a later time, if you need to create connections to the applications whose APIs you did not enable, you can return here and follow the instructions to enable their APIs.
You must create a new Google client application for Fuse Online. The credentials that Google provides for a new client application contain a refresh token that is used for refreshing expired access tokens. This refresh token is available only the first time that the Fuse Online client application uses the credentials. In Fuse Online, connections to Gmail, Google Calendar, and Google Sheets can all use the same Google client ID and Google client secret. If they do, the refresh token is available to all connections to Google applications. When you view connection details in the Fuse Online user interface, do not click the Validate button. Validation is a second use of the credentials and the refresh token is no longer part of the client credentials. You can, however, re-connect to Google applications.
In development environments, be careful if you choose to use the Google client ID and Google client secret that you are using for some other, non-Fuse Online, OAuth client. Fuse Online requires offline access that is requested on the first OAuth exchange. If another OAuth client already entered the OAuth exchange and did not request offline access, then Fuse Online cannot obtain offline access on subsequent OAuth exchanges. If you are unsure whether offline access was requested on the first exchange, create a new Google client application for Fuse Online.
Prerequisites
- You must be able to sign in to the Google account that you want to use to register Fuse Online as a Google client application.
Procedure
In Fuse Online:
- In the left navigation panel, click Settings.
- On the Settings page, near the top, to the right of the callback URL, click to copy the callback URL for your Fuse Online environment to the clipboard. You will need this URL later in this procedure.
In another browser tab, go to
https://console.developers.google.com
and do the following:Check that you are signed into the Google account that you want to use to register Fuse Online as a Google client application. Or, choose a different Google account and sign in to that account.
The name of the current Google project appears at the top of the Google APIs page.
- To use the current project to grant authorization to Fuse Online, continue to the next step. To grant authorization to Fuse Online in another Google project, choose or create that project. If this Google account does not already have a project, you must create one.
Enable Google APIs:
- In the upper left corner, click and select APIs and Services > Library.
- If necessary, scroll down to see the G Suite row of cards.
- Click the Google Calendar API card, which displays a page that indicates that the Google Calendar API is enabled.
- Scroll down and click the Gmail API card, which displays a page that indicates that the Gmail API is enabled.
- In the upper left corner, click and select APIs and Services > Library.
- If necessary, scroll down to see the G Suite row of cards.
- Click the Google Sheets API card, which displays a page that indicates that the Google Sheets API is enabled.
Give your client application a name:
- Click MANAGE.
- In the page that appears, in the left navigation panel, click Credentials and then on the right, click Credentials in APIs & Services.
- Click the OAuth consent screen tab.
-
In the page that appears, in the Application Name field, enter a name for the Fuse Online client application. For example, enter
Fuse Online client application
. - Skip the other fields.
- Click Save.
Obtain client application credentials as follows:
- To the right of Create Credentials, click the down arrow to display a menu and select OAuth client ID.
- In the page that appears, select Web application to display more content.
-
In the Name field, enter a name for the OAuth client ID for your Fuse Online environment. This is different from the name that you entered for the client application itself. For example, enter
OAuth client ID for Fuse Online
. - Skip Authorized JavaScript origins.
- In the Authorized redirect URIs field, paste the callback URL that you copied from your Fuse Online environment at the beginning of this procedure.
- Click Create to display the client ID and client secret for your Fuse Online environment.
- To the right of the client ID field, click to copy the client ID to your clipboard.
Return to the Fuse Online Settings page and do the following:
- Expand the entries for Gmail, Google Calendar, and Google Sheets.
- In the Client ID field for each Google application, paste the Google client ID that you just copied.
- Return to the Google developers site and to the right of the client secret field, click to copy the client secret to your clipboard.
Return to the Fuse Online Settings page and do the following in the entry for each Google application:
- In the Client Secret field, paste the Google client secret that you just copied.
- Click Save. You should get a Registration Successful! notification.
- Click Ok to collapse the entry.
Results
For each Google application API that you enabled, you can create a connection to that application.
All connections from Fuse Online to Google applications use the same Google client ID and Google client secret.
The Google client ID and Google client secret contain token refresh information to ensure that integrations that have connections to Google applications continuously work correctly. Consequently, you should not obtain new credentials. If you do, then you would need to recreate each Google connection, replace the old connections with new connections, and re-publish each integration that uses a Google connection.
8.2. Connecting to Gmail
To trigger execution of an integration when a particular Gmail account receives an email, add a Gmail connection to a simple integration as its start connection. In an integration, to send an email from a particular Gmail account, do either of the following:
- Add a Gmail connection to the middle of a flow.
- Add a Gmail connection to finish a simple integration.
The general steps for connecting to Gmail in an integration are:
- Registering Fuse Online as a Google client application.
- Creating a Gmail connection. When you do this you choose the Gmail account that the connection is authorized to access.
- If your integration sends an email from a Gmail account, decide how to populate an email to send.
- Adding a Gmail connection to an integration flow.
- For a Gmail connection that sends an email, optionally mapping integration data to the email fields.
Information and instructions are in the following topics:
8.2.1. Creating a Gmail connection
When you create a Gmail connection, you authorize the connection to access one particular Gmail account. After you create a Gmail connection, you can add it to multiple integrations.
Prerequisites
- You registered Fuse Online as a Google client application and enabled the Gmail API.
- The Fuse Online Settings page entry for Gmail has values for the client ID and client secret, which you obtained by registering Fuse Online as a Google client application.
Procedure
- In Fuse Online, in the left panel, click Connections to display any available connections.
- In the upper right, click Create Connection to display the available connectors. A connector is a template that you use to create one or more connections.
- Click the Gmail connector.
In the Configure Connection page, click Connect Gmail, which takes you to a Sign in with Gmail page.
If Connect Gmail does not display, your Fuse Online environment is not registered as a Google client application. See Registering Fuse Online as a Google client application. When you try to create a Gmail connection and your Fuse Online environment is not registered as a Google client application, then Fuse Online displays multiple fields that prompt for authorization information. While you can create a Gmail connection by entering values in these fields, it is not recommended.
- In the page that is prompting you to sign in to Gmail, enter the email address of the Google account that you want this connection to access from Fuse Online and click Next.
- In response to openshiftapps.com wants to access your Google Account, click Allow to return to Fuse Online.
-
In the Fuse Online Connection Name field, enter your choice of a name that helps you distinguish this connection from any other connections. For example, enter
Gmail Connect 1
. -
In the Description field, optionally enter any information that is helpful to know about this connection. For example, enter
Sample Gmail connection that uses jkim Gmail account credentials.
- In the upper right, click Create to see that the connection you created is now available. If you entered the example name, you would see that Gmail Connect 1 appears as a connection that you can choose to add to an integration.
8.2.2. Alternatives for populating email to send
A Gmail connection that finishes a simple integration or that is in the middle of a flow sends an email from the Gmail account that the connection is authorized to access. There are several ways to populate the content of the email that the connection sends. Before you add a Gmail connection that sends an email, consider how you want to populate that email.
The alternatives for populating an email to send are as follows:
Add a data mapper step just before the Gmail connection that sends an email. In this data mapper step, map data fields that are output from previous steps to Gmail connection Send Email action fields. The Send Email action fields are:
- Email to
- Email subject
- Email text
- Email cc
- Email bcc
If you add a data mapper step then you can map one, some, or all Send Email action fields.
- When you add a Gmail connection to a flow, configure the action by specifying values in the Send Email action fields. You can specify values in one, some, or all fields.
Use both a data mapper step and Send Email action configuration to populate the fields. In other words, you can configure the Send Email action by specifying one or more fields and also add a data mapper step that populates other fields.
A value that you specify directly in a Send Email action field has precedence over a value that is mapped to the Send Email action field. In other words, suppose you populate a Send Email field by specifying a value when you add the connection and configure the action and also by mapping a value to the same field. The value that you specify in the action configuration always overrides the value that was mapped. For example, suppose you specify
people@redhat.com
in the Email to action field and you also map an email field from a previous step to the Gmail Email to field. The integration always usespeople@redhat.com
as the email address.
When you add a Gmail connection that sends an email, all action configuration parameters are optional. This is because you might choose to populate an email entirely by mapping integration data to the Send Email action fields. However, the presence of an email address in the Email to field, either by action configuration specification or by mapping, is required. Without an email address to send the message to, Fuse Online generates a runtime error and the integration stops executing.
8.2.3. Triggering an integration when polling returns a Gmail message
To trigger execution of an integration based on email received by a particular Gmail account, add a Gmail connection as the start connection of a simple integration. When the integration is running, the Gmail connection checks this account for emails at intervals that you control. When the connection finds an unread email, it passes the email to the next step in the integration and, by default, marks the email as read.
Prerequisites
- You created a Gmail connection that is authorized to access the Gmail account that you want to obtain emails from.
Procedure
- In the Fuse Online panel on the left, click Integrations.
- Click Create Integration.
- On the Choose a Start Connection page, click the Gmail connection that you want to use to start the integration.
- On the Choose an Action page, click the Receive Email action.
To configure the Receive Email action:
- In the Delay field, accept the default of 30 seconds or specify how often you want the integration to check for new email.
- Leave the Labels field blank to obtain any unread email. To obtain only certain emails, specify a comma-separated list of labels that are used in the Gmail account that the connection is accessing. By default, the integration obtains the unread emails that have those labels.
- Select Mark as read to ensure that the connection does not return the same email twice. If Mark as read is not selected, the connection returns emails that are in the account’s in box, whether or not they were previously read. Or, if you specify one or more labels, then the integration return emails that have those labels whether or not they were already read.
In the Max Results field, accept the default of 5 or indicate the maximum number of emails that the connection can return for each poll. If the connection finds more than this number of unread emails, then it returns the most recent Max Results emails.
When a Gmail connection returns more than one email, the integration processes the emails as a batch. In other words, Fuse Online executes the integration once for the batch.
- Click Done to add this Gmail connection as the start connection in the integration. The connection appears as the first step in the integration flow.
8.2.4. Sending an email from a Gmail account
In an integration, you can send an email from a Gmail account either in the middle of a flow or to finish a simple integration. To do this, add a Gmail connection to the middle of a flow or as a simple integration’s finish connection.
Prerequisites
- You created a Gmail connection.
- You are familiar with the alternatives for populating an email to send and you have a plan for populating such emails.
- Fuse Online is prompting you to choose a step or to choose the finish connection for a simple integration.
Procedure
- Click the Gmail connection that you want to use to send an email.
- On the Choose an Action page, click Send Email.
On the Configure Send Email page, do one of the following:
- Leave all fields blank if you plan to add a data mapper step just before this connection and you plan to map integration data to the Send Email fields.
Enter information in one or more of the following Send Email fields. Separate multiple email addresses with a comma.
- Email To
- Email Subject
- Email Text
- Email cc
- Email bcc
Leave some fields blank and enter information in some fields.
If you plan to add a data mapper step just before this Gmail connection, and you want to map integration data to some email fields, leave those fields blank in the action configuration. Enter values in other action fields as needed.
A value that you specify in a Send Email action configuration field has precedence over a value that is mapped from a previous step.
- Click Done to add the connection to the flow.
Result and next steps
The connection appears in the integration flow in the location where you added it. If you plan to add a data mapper step before this connection, first add any other connections that you plan to add to this integration. Then add the data mapper step.
8.3. Connecting to Google Calendar
To trigger integration execution when a poll returns an update to a Google calendar, add a Google Calendar connection to a simple integration as its start connection. To add an event to a calendar or update an event in a calendar, you can do either of the following:
- Add a Google Calendar connection to the middle of a flow.
- Add a Google Calendar connection to finish a simple integration.
Details for connecting to Google Calendar are in the following topics:
- Section 8.3.1, “Creating a Google Calendar connection”
- Section 8.3.2, “Triggering an integration when polling returns an event from a Google Calendar”
- Section 8.3.3, “Obtaining a particular event from a Google Calendar”
- Section 8.3.5, “Updating an event in a Google Calendar”
- Section 8.3.4, “Adding an event to a Google Calendar”
8.3.1. Creating a Google Calendar connection
When you create a Google Calendar connection, you authorize the connection to access the Google Calendars that are associated with one particular Google account. After you create a Google Calendar connection, you can add it to multiple integrations.
Prerequisites
- You registered Fuse Online as a Google client application and enabled the Google Calendar API.
- The Fuse Online Settings page entry for Google Calendar has values for the client ID and client secret, which you obtained by registering Fuse Online as a Google client application.
Procedure
- In Fuse Online, in the left panel, click Connections to display any available connections.
- In the upper right, click Create Connection to display the available connectors. A connector is a template that you use to create one or more connections.
- Click the Google Calendar connector.
In the Configure Connection page, click Connect Google Calendar, which takes you to a Google sign-in page.
If Connect Google Calendar does not display, then your Fuse Online environment is not registered as a Google client application with the Google Calendar API enabled. See Registering Fuse Online as a Google client application. When your environment is not registered with Google, then when you try to create a Google Calendar connection, Fuse Online displays multiple fields that prompt for authorization information. While you can create a Google Calendar connection by entering values in these fields, it is not recommended.
- In the Google sign-in page, select the Google account that you want this connection to access from Fuse Online and click Next.
- In response to the openshiftapps.com wants to access your Google Account prompt, click Allow to return to Fuse Online.
-
In the Fuse Online Connection Name field, enter your choice of a name that helps you distinguish this connection from other connections. For example, enter
Google Calendar Work Connection
. -
In the Description field, optionally enter any information that is helpful to know about this connection. For example, enter
Sample Google Calendar connection that uses my Google work account.
- In the upper right, click Create to see that the connection you created is now available. If you entered the example name, you would see that Google Calendar Work Connection appears as a connection that you can choose to add to an integration.
8.3.2. Triggering an integration when polling returns an event from a Google Calendar
To trigger execution of an integration upon obtaining events from a Google Calendar that you specify, add a Google Calendar connection to a simple integration as its start connection. When the integration is running, the Google Calendar connection checks the Google Calendar for events at intervals that you control. When the connection finds events that comply with the way that you configured the Google Calendar Get Events action, the connection passes the events to the next step in the integration.
When a Google Calendar connection returns more than one event, Fuse Online executes the integration for each returned event. For example, if the poll returns 5 events then Fuse Online executes the integration five times.
Prerequisites
- You created a Google Calendar connection.
Procedure
- In the Fuse Online panel on the left, click Integrations.
- Click Create Integration.
- On the Choose a Start Connection page, click the Google Calendar connection that you want to use to start the integration.
- On the Choose an Action page, click the Get Events action.
To configure the Get Events action:
- In the Delay field, accept the default of 30 seconds or specify how often you want the integration to check the calendar.
- In the Calendar name field, enter the name of a calendar that is accessible from the Google account that this Google Calendar connection is authorized to access.
- In the Max Results field, accept the default of 5 or indicate the maximum number of events that the connection can return for each poll.
Select one of the following to indicate the date that you want the connection to use to start checking for events. The connection checks that date and subsequent dates.
Consume from the current date ahead starts on the current date.
For example, if you select this option, and if Max Results is 5, then the Google Calendar connection starts checking for events on the current date and returns no more than the first five events that it finds. It does not matter whether or not these events have been updated or added since the last poll.
Consume from the last event update date on the next poll starts on the date of the most recently updated event that was returned in a previous poll.
For example, if you select this option, and if Max Results is 5, then the Google Calendar connection returns no more that 5 updated or new events. The connection starts checking for new or updated events on the date of the most recently updated event that was returned in a previous poll.
Optionally, in the Query for events field, specify text to filter the events that the poll can return. The connection returns only events that contain the specified text in at least one event field.
For example, suppose that you specify
Standup meeting
in the query field. A poll would return only those events that haveStandup meeting
in an event field.
- Click Done to add this Google Calendar connection as the integration’s start connection. The connection appears as the first step in the integration.
8.3.3. Obtaining a particular event from a Google Calendar
In an integration, you can obtain a particular Google Calendar event in the middle of a flow. Obtaining a particular event is useful, for example, when you want to:
- Update the event in a subsequent Google Calendar connection.
- Announce the event by using a subsequent Twitter connection.
To obtain one event, add a Google Calendar connection to the middle of a flow.
In this release, while obtaining a specific event in a simple integration’s finish connection is supported, it is not particularly useful. This is expected to change in a future release.
Prerequisites
- A Google Calendar connection is available and this connection is authorized to access the Google Calendar that has the event that that you want to get.
- You are creating or editing a flow and Fuse Online is prompting you to choose a step.
Procedure
- Click a Google Calendar connection that is authorized to access the calendar that you want to connect to.
- On the Choose an Action page, click Get a Specific Event.
- Specify the name of the calendar that has the event that you want.
Specify the ID of the event to get. To obtain the event ID, do one of the following:
- Map it from a previous Google Calendar connection.
Manually obtain it from the calendar as follows:
- In a browser, display the calendar that contains the event you want to get.
-
Append
?gsessionid=OK&eventdeb=1
to the URL and redisplay the calendar. - In the calendar, click the event that you want to get.
- In the event popup, click and select Troubleshooting info.
-
In the popup that Google Calendar displays, copy the string that follows
eid=
. For example, an event ID looks something like this:p1pva2a4t504gbsha12di9ch6k_20181107T150000Z*
.
- Click Done to add the connection to the flow. The connection appears in the flow in the location where you added it.
8.3.4. Adding an event to a Google Calendar
In an integration, you can add an event to a Google Calendar in the middle of a flow or to finish a simple integration. To do this, add a Google Calendar connection to the middle of a flow or as a simple integration’s finish connection.
Prerequisites
- A Google Calendar connection is available and this connection is authorized to access the Google Calendar to which you want to add an event.
- You are creating or editing a flow and Fuse Online is prompting you to choose a step. Or, Fuse Online is prompting you to choose a finish connection.
Procedure
- Click a Google Calendar connection that is authorized to access the calendar that you want to add an event to.
- On the Choose an Action page, click the Create Event action.
Configure the Create Event action:
- Enter a title for the new event.
- Enter the name of the Google Calendar to add the event to.
- For the other input parameters, you can enter data or you might want to map values from an event that you obtained in a previous Google Calendar connection.
- Click Done to add the connection to the flow. The connection appears in the integration flow in the location where you added it.
8.3.5. Updating an event in a Google Calendar
In an integration, you can update an event in a Google Calendar in the middle of a flow or to finish a simple integration. To do this, add a Google Calendar connection to the middle of a flow or as a simple integration’s finish connection.
In this release, the Update Event action requires a value in each event field. In most if not all cases, this means that you must add a Google Calendar connection that obtains the event that you want to update, then add the Google Calendar connection that updates the event, and then insert a data mapper step between the two Google Calendar connections.
Prerequisites
- A Google Calendar connection is available and this connection is authorized to access the Google Calendar that has the event that you want to update.
- In the flow, there is an earlier connection to Google Calendar and that connection obtains the event that you want to update.
- You are creating or editing a flow and Fuse Online is prompting you to choose a step. Or, Fuse Online is prompting you to choose a finish connection.
Procedure
- Click a Google Calendar connection that is authorized to access the calendar that has the event that you want to update.
- On the Choose an Action page, click Update Event.
To configure the Update Event action:
- Enter the title of the event that you want to update.
- Enter content in each event field that you want to update. Do not enter content in an event field when you want the content in that field to remain unchanged.
- Click Done to add the connection to the flow. The connection appears in the integration visualization flow in the location where you added it.
- In the integration visualization flow, click the plus sign that is just before the connection that you just added.
- Click Data Mapper.
In the data mapper, for each event field that remains the same in the updated event, map that field from the Google Calendar connection that obtained the event to the corresponding field in the Google Calendar connection that updates the event.
Do not map fields that you are updating. If you map a field and also specify an update for that field when you configure the Update Event action, Fuse Online uses the mapped value.
- In the upper right, click Done to add the data mapper step.
8.4. Connecting to Google Sheets
To trigger execution of an integration when a Google Sheets connection returns spreadsheet data or spreadsheet properties, add a Google Sheets connection to a simple integration as its start connection. To finish a simple integration by updating spreadsheet values or properties, or by inserting a chart or pivot table into a spreadsheet, add a Google Sheets connection to a simple integration as its finish connection.
In the middle of a flow, you can add a Google Sheets connection that obtains, creates or updates values in a spreadsheet, or that inserts a chart or pivot table into a spreadsheet.
Details for connecting to Google Sheets are in the following topics:
- Section 8.4.1, “Creating a Google Sheets connection”
- Section 8.4.2, “Triggering an integration when polling returns spreadsheet data”
- Section 8.4.3, “Triggering an integration when polling returns spreadsheet properties”
- Section 8.4.4, “Creating a spreadsheet”
- Section 8.4.5, “Updating data in a sheet”
- Section 8.4.6, “Appending data to a sheet”
- Section 8.4.7, “Updating spreadsheet properties”
- Section 8.4.8, “Adding a chart to a sheet”
- Section 8.4.9, “Adding a pivot table to a sheet”
8.4.1. Creating a Google Sheets connection
When you create a Google Sheets connection, you authorize the connection to access the Google Sheets spreadsheets that are associated with one particular Google account, which you choose. After you create a Google Sheets connection, you can add it to multiple integrations.
Prerequisites
- You registered Fuse Online as a Google client application and enabled the Google Sheets API.
- The Fuse Online Settings page entry for Google Sheets has values for the client ID and client secret, which you obtained by registering Fuse Online as a Google client application.
Procedure
- In Fuse Online, in the left panel, click Connections to display any available connections.
- In the upper right, click Create Connection to display the available connectors. A connector is a template that you use to create one or more connections.
- Click the Google Sheets connector.
In the Configure Connection page, click Connect Google Sheets, which takes you to a Google sign-in page.
If Connect Google Sheets does not display, then your Fuse Online environment is not registered as a Google client application with the Google Sheets API enabled. See Registering Fuse Online as a Google client application. When your environment is not registered with Google, then when you try to create a Google Sheets connection, Fuse Online displays multiple fields that prompt for authorization information. While you can create a Google Sheets connection by entering values in these fields, it is not recommended.
- In the Google sign-in page, select the Google account that you want this connection to access from Fuse Online and click Next.
- In response to the openshiftapps.com wants to access your Google Account prompt, click Allow to return to Fuse Online.
-
In the Fuse Online Connection Name field, enter your choice of a name that helps you distinguish this connection from other connections. For example, enter
Google Sheets Work Connection
. -
In the Description field, optionally enter any information that is helpful to know about this connection. For example, enter
Sample Google Sheets connection that uses my Google work account.
- In the upper right, click Create to see that the connection that you created is now available. If you entered the example name, you would see that Google Sheets Work Connection appears as a connection that you can choose to add to an integration.
8.4.2. Triggering an integration when polling returns spreadsheet data
To trigger execution of an integration upon obtaining data from a Google Sheets spreadsheet, add a Google Sheets connection to a simple integration as its start connection. When the integration is running, the Google Sheets connection polls the spreadsheet at the interval that you specified, obtains the data that you identified, and passes the data to the next step in the integration.
To obtain data from a particular sheet in a spreadsheet, you specify the sheet name when you configure the Get sheet values action for the Google Sheets connection. A particular connection can obtain data from only one sheet.
Between polls, if there are no changes to the sheet values that the connection is configured to return, then the next poll returns the same values as the previous poll.
Prerequisites
- You created a Google Sheets connection that is authorized to access the spreadsheet that you want to obtain data from.
Procedure
- In the Fuse Online panel on the left, click Integrations.
- Click Create Integration.
- On the Choose a Start Connection page, click the Google Sheets connection that you want to use to start the integration.
- On the Choose an Action page, click the Get sheet values action.
To configure the Get sheet values action:
In the SpreadsheetId field, enter the ID of a Google spreadsheet that is accessible from the Google account that this Google Sheets connection is authorized to access.
To obtain a spreadsheet ID, display the spreadsheet in a browser. The ID is the part of the URL between
d/
and/edit
.In the Range field, enter Google A1 notation that identifies the data that you want to obtain. The default is A:A.
For example, suppose that you want all data from a spreadsheet that has one sheet with 4 columns. Specify
A:D
. Or, to obtain data from rows 5 through 15, specifyA5:D15
.If the spreadsheet from which you are obtaining data has more than one sheet, specify the sheet name as well as the start cell coordinate and the end cell coordinate. If there is more than one sheet, and you do not specify a sheet name, then the connection obtains data from the first sheet in the spreadsheet. For example, specification of
2019!A1:D5
specifies that you want to obtain data from the sheet whose name is2019
. In that sheet, you want the data that is in columns A through D for rows 1 through 5.- In the Major Dimension field, accept Rows, which is the default, or select Columns. Rows configures the action to return a collection of row objects where each row object contains a value for each desired column. Columns configures the action to return a collection of column objects where each column object contains a value for each desired row.
In the Split Results field, accept No, which is the default, or select Yes. A setting of No configures the action to return data as a collection of values. That is, the connection passes a collection of row objects or a collection of column objects to the next step in the flow. Select Yes to enable the connection to split the returned data according to the setting of Major Dimension. For example, if Major Dimension is set to Rows then the connection returns row objects. Each row object triggers a separate execution of the flow. That is, Fuse Online executes the flow once for each returned row object. For example, if the poll returns 5 rows then Fuse Online executes the flow 5 times.
Fuse Online also provides discrete split and aggregate steps, which you can add to a flow. If you want to process individual objects in one or more steps and then aggregate the row or column objects, do not split the Google Sheets connection result. Instead, accept the default, No, and then add a split step to the flow after this connection. A split step is required if you want an aggregate step in the flow.
- In the Delay field, accept the default of 30 seconds or specify how often you want the connection to obtain spreadsheet data.
In the Max Results field, accept the default of 25 or specify another limit for the minor dimension in the result matrix.
For example, suppose that the Major Dimension is columns and that Max Results is 25. The poll returns a column object for each column that the range setting includes. Each column object contains no more than 25 row values.
In this release, it is a known problem that the setting for Max Results is observed only when Split Results is set to Yes. When Split Results is set to No, the setting of Max Results is ignored. In the next release, observance of the Max Results setting will not depend on the Split Results setting.
- Click Done to add this Google Sheets connection as the integration’s start connection. In the integration visualization panel, the connection appears as the first step in the integration.
Next steps
After you add a start connection, Fuse Online prompts you to add the integration’s finish connection. With the start and finish connections in the integration, add any other connections that you want in the integration. Then, after the connection that obtains sheet values, add a data mapper step. In the data mapper, Fuse Online displays source fields according to how you configure the Get sheet values action. That is, if the major dimension is Rows, then the data mapper lists the column names as fields that you can map to the target. If the major dimension is COLUMNS, then the data mapper lists row indexes as fields that you can map to the target.
Additional resource
Google A1 notation for specifying groups of cells in a spreadsheet
8.4.3. Triggering an integration when polling returns spreadsheet properties
To trigger execution of an integration upon obtaining properties from a Google Sheets spreadsheet, add a Google Sheets connection to a simple integration as its start connection. When the integration is running, the Google Sheets connection polls the spreadsheet at the interval that you specified, obtains the spreadsheet properties, and passes the result to the next step in the integration.
Properties include the spreadsheet’s title, locale, and time zone. Between polls, if there are no changes to the sheet properties, then the next poll returns the same values as the previous poll.
Prerequisites
- You created a Google Sheets connection that is authorized to access the spreadsheet that you want to obtain properties from.
Procedure
- In the Fuse Online panel on the left, click Integrations.
- Click Create Integration.
- On the Choose a Start Connection page, click the Google Sheets connection that you want to use to start the integration.
- On the Choose an Action page, click the Get spreadsheet properties action.
To configure the Get spreadsheet properties action:
In the SpreadsheetId field, enter the ID of a Google spreadsheet that is accessible from the Google account that this Google Sheets connection is authorized to access.
To obtain a spreadsheet ID, display the spreadsheet in a browser. The ID is the part of the URL between
d/
and/edit
.- In the Delay field, accept the default of 30 seconds or specify how often you want the connection to obtain properties.
- Click Done to add this Google Sheets connection as the integration’s start connection. In the integration visualization panel, the connection appears as the first step in the integration’s flow.
8.4.4. Creating a spreadsheet
To create a new spreadsheet in the middle of a flow, add a Google Sheets connection between the start and finish connections. While you can also finish a simple integration with a Google Sheets connection that creates a spreadsheet, you cannot add data to a new spreadsheet in the same connection. Therefore, when you want to create a spreadsheet and add data to the spreadsheet in the same flow, the flow requires two Google Sheets connections. One connection creates the spreadsheet and then a subsequent connection adds data to the spreadsheet.
Prerequisites
- You created a Google Sheets connection that is authorized to access the Google account in which you want to create the spreadsheet.
- You are creating or editing a flow and Fuse Online is prompting you to choose a step. Or, Fuse Online is prompting you to choose a finish connection.
Procedure
- Click a Google Sheets connection that is authorized to access the Google account that you want to use to create the spreadsheet.
- On the Choose an Action page, click Create spreadsheet.
To configure the Create spreadsheet action:
- In the Title field, enter the title of the new spreadsheet.
-
In the Time Zone field, enter the time zone that the new spreadsheet should use, for example,
India Standard Time
, orTokyo
. -
In the Locale field, enter the locale of the new spreadsheet, for example,
Canada
, orHong Kong
.
- Click Done to add the connection to the flow. The connection appears in the integration visualization flow in the location where you added it.
Result
A connection that creates a spreadsheet returns the spreadsheet ID for the new spreadsheet. In subsequent steps, you can map this spreadsheet ID to the spreadsheet ID in a connection that accesses the new spreadsheet, for example, to update it.
8.4.5. Updating data in a sheet
In an integration, you can update data in a spreadsheet in the middle of a flow or to finish a simple integration. To do this, add a Google Sheets connection to the middle of a flow or as a simple integration’s finish connection.
Prerequisites
- You created a Google Sheets connection that is authorized to access the Google spreadsheet that you want to update.
- You have access to the spreadsheet ID for the spreadsheet that you want to update.
- You are creating or editing a flow and Fuse Online is prompting you to choose a step. Or, Fuse Online is prompting you to choose a finish connection.
Procedure
- Click a Google Sheets connection that is authorized to access the spreadsheet that you want to update.
- On the Choose an Action page, click Update sheet values.
To configure the Update sheet values action:
In the SpreadsheetId field, do one of the following:
- Enter the ID of the spreadsheet that you want to update.
- Leave the field blank. In this case, after you add this connection to the flow, you must add a data mapper step before this connection. In the data mapper step, map the spreadsheet ID from a previous connection to this connection. The previous connection must access the spreadsheet that you want to update in this connection.
In the Range field, use Google A1 notation to specify the spreadsheet cells that you want to update. Specify the start and end coordinates, for example,
A1:D4
updates the first 4 columns in the first 4 rows in the first sheet in the spreadsheet.The default is
A:A
, which updates the first column in the first sheet in the spreadsheet.- In the Major Dimension field, accept Rows, which is the default, or select Columns. Rows configures the action to use row objects to update the sheet. Each row object contains a value for each column that you want to update. Columns configures the action to use column objects to update the sheet. Each column object contains a value for each row that you want to update.
In the Value Input Option field, indicate how you want Google sheets to interpret the data that it receives for updating the sheet. Unspecified, which is the default, enables Google Sheets to automatically convert data that it recognizes. For example, if the input data is a date, then Google Sheets formats it as a date. If the input data is a decimal, then Google Sheets formats it as a decimal.
- Unspecified defaults to the Google Sheets API setting, which defaults to User entered.
- Raw causes Google Sheets to insert the input data as is.
- User entered enables automatic conversion of recognizable data.
- Click Done to add the connection to the flow. The connection appears in the integration visualization flow in the location where you added it.
- If you want to add any other connections to the flow, add them now and then return to these instructions.
- After the flow has all desired connections, in the integration visualization flow, click the plus sign that is just before the Google Sheets connection that updates sheet values.
- Click the Data Mapper card to add a data mapping step to the flow.
In the data mapper:
-
If you did not specify the spreadsheet ID when you configured the Update sheet values action, then map a source
spreadsheetId
to the targetspreadsheetId
. Specify the data that you want to use to update the sheet by mapping fields from a source step to the target spreadsheet. For example, you might map fields from another spreadsheet or from a database.
If you need to, you can edit the Google Sheets connection that updates sheet values and change the settings for Range or Major Dimension. Changing these settings causes the data mapper to display different target fields according to your changes.
- In the upper right, click Done to add the data mapper step.
-
If you did not specify the spreadsheet ID when you configured the Update sheet values action, then map a source
8.4.6. Appending data to a sheet
You can append data to a sheet in the middle of a flow or to finish a simple integration. To do this, add a Google Sheets connection to the middle of a flow or as a simple integration’s finish connection.
Prerequisites
- You created a Google Sheets connection that is authorized to access the Google spreadsheet in which you want to append data.
- You have access to the ID for the spreadsheet in which you want to append data.
- You are creating or editing a flow and Fuse Online is prompting you to choose a step. Or, Fuse Online is prompting you to choose a finish connection.
Procedure
- Click a Google Sheets connection that is authorized to access the spreadsheet in which you want to append data.
- On the Choose an Action page, click Append values to a sheet.
To configure the Append values to a sheet action:
In the SpreadsheetId field, do one of the following:
- Enter the ID of the spreadsheet that you want to append values to.
- Leave the field blank. In this case, after you add this connection to the flow, you must add a data mapper step before this connection. In the data mapper step, map the spreadsheet ID from a previous connection to this connection. The previous connection must access the spreadsheet that you want to append data to in this connection.
In the Range field, use Google A1 notation to specify the spreadsheet range that you want to append data to. Specify the start and end coordinates, for example,
A3:D3
appends data to the first sheet, after the third row for 4 columns. The default isA:A
, which appends data to the first column in the first sheet in the spreadsheet.The connection never overwrites data. The connection starts appending data after the range you specify, and then always appends data to the content that is in place.
- In the Major Dimension field, accept Rows, which is the default, or select Columns. Rows configures the action to use row objects to append data. Each row object contains a value for each column that you want to append data to. Columns configures the action to use column objects to append data. Each column object contains a value for each row that you want to append.
In the Value Input Option field, indicate how you want Google sheets to interpret the data that it receives for appending to a sheet. Unspecified, which is the default, enables Google Sheets to automatically convert data that it recognizes. For example, if the input data is a date, then Google Sheets formats it as a date. If the input data is a decimal, then Google Sheets formats it as a decimal.
- Unspecified defaults to the Google Sheets API setting, which defaults to User entered.
- Raw does nothing. Google Sheets inserts the input data as is.
- User entered enables automatic conversion of recognizable data.
Click Done to add the connection to the flow. The connection appears in the integration visualization flow in the location where you added it.
The values that you specify to configure the Append values to a sheet action determine the fields that the data mapper displays for mapping to or from this connection.
- If you want to add any other connections to the flow, add them now and then return to these instructions.
- After the flow has all desired connections, in the integration visualization flow, click the plus sign that is just before the Google Sheets connection that appends data to a sheet.
- Click the Data Mapper card to add a data mapping step to the flow.
In the data mapper:
-
If you did not specify the spreadsheet ID when you configured the Append values to a spreadsheet action, then map a source
spreadsheetId
to the targetspreadsheetId
. Specify the data to append to the sheet by mapping fields from a source step to the target spreadsheet. For example, you might map fields from another spreadsheet or from a database.
If you need to, you can edit the Google Sheets connection that appends sheet values and change the settings for Range or Major Dimension. Changing these settings causes the data mapper to display different target fields according to your changes.
- In the upper right, click Done to add the data mapper step.
-
If you did not specify the spreadsheet ID when you configured the Append values to a spreadsheet action, then map a source
8.4.7. Updating spreadsheet properties
In an integration, you can update the properties of a spreadsheet in the middle of a flow or to finish a simple integration. To do this, add a Google Sheets connection to the middle of a flow or as a simple integration’s finish connection.
Properties include the spreadsheet’s title, locale, and time zone.
When Fuse Online prompts you to configure the Update spreadsheet properties action, you can leave some or all fields blank. If you leave a field blank, then in a data mapper step that is in the flow (you add it later) before this connection, you map fields from previous steps to blank Update spreadsheet properties action configuration fields.
Prerequisites
- You created a Google Sheets connection that is authorized to access the Google spreadsheet whose properties you want to update.
- You have access to the spreadsheet ID for the spreadsheet whose properties you want to update.
- You are creating or editing a flow and Fuse Online is prompting you to choose a step. Or, Fuse Online is prompting you to choose a finish connection.
Procedure
- Click a Google Sheets connection that is authorized to access the spreadsheet whose properties you want to update.
- On the Choose an Action page, click Update spreadsheet properties.
To configure the Update spreadsheet properties action:
In the SpreadsheetId field, do one of the following:
- Enter the ID of the spreadsheet whose properties you want to update.
- Leave the field blank. In this case, after you add this connection to the flow, you must add a data mapper step that is before this connection. In the data mapper step, map the spreadsheet ID from a previous connection to this connection. The previous connection must access the spreadsheet that you want to update in this connection.
- In the other fields, enter a value only if you want to change the property. Alternatively, you can leave the fields blank. If you do, then in a data mapper step that you add later, just before this connection, you can map the fields that you want to change.
- Click Done to add the connection to the flow. The connection appears in the integration visualization flow in the location where you added it.
- If you want to add any other connections to the flow, add them now and then return to these instructions.
After the flow has all desired connections, you might want to use a data mapper step to update spreadsheet properties:
- In the integration visualization flow, click the plus sign that is just before the Google Sheets connection that updates properties.
- Click Data Mapper to add a data mapping step to the flow.
-
In the data mapper, if you did not specify a spreadsheet ID when you configured the Update spreadsheet properties action, map a source
spreadsheetId
to the targetspreadsheetId
. - For each spreadsheet property that you want to change and for which you did not specify an updated value when you configured the Update spreadsheet properties action, map a field from a source step to the target spreadsheet property that you want to change.
- In the upper right, click Done to add the data mapper step.
8.4.8. Adding a chart to a sheet
In the middle of a flow, or to finish a simple integration, you can add a basic chart or a pie chart to a Google Sheets spreadsheet. To do this, add a Google Sheets connection to the middle of a flow or as a simple integration’s finish connection. Then add a data mapping step just before the connection. In the data mapping step, set options that determine the location, properties, and content of the chart.
When Fuse Online prompts you to configure the Add charts action, you can leave some or all fields blank. If you leave a field blank, you can map a value to that field in the data mapper step that you will add just before this connection.
Prerequisites
- You created a Google Sheets connection that is authorized to access the Google spreadsheet that you want to add a chart to.
- You have access to the ID for the spreadsheet that you want to add a chart to.
- You are creating or editing a flow and Fuse Online is prompting you to choose a step. Or, Fuse Online is prompting you to choose the finish connection for a simple integration.
Procedure
- Click a Google Sheets connection that is authorized to access the spreadsheet that you want to add a chart to.
- On the Choose an Action page, click Add charts.
To configure the Add charts action:
In the SpreadsheetId field, do one of the following:
- Enter the ID of the spreadsheet that you want to add a chart to.
- Leave the field blank. In the data mapper step that will be just before this connection, map the spreadsheet ID from a previous connection to this connection. The previous connection must access the spreadsheet that you want to add a chart to in this connection.
In the Chart Title field, enter a title.
Alternatively, leave the field blank. In the data mapper step that is just before this connection, map the chart title from a source field or property to the
title
target field.In the Subtitle field, enter a subtitle.
Alternatively, leave the field blank. In the data mapper step that is just before this connection, map the chart subtitle from a source field or property to the
subtitle
target field.
- Click Done to add the connection to the flow. The connection appears in the integration visualization flow in the location where you added it.
- If you want to add any other connections to the flow, add them now. Then return to these instructions.
- In the integration visualization flow, click the plus sign that is just before the connection that adds a chart.
Click Data Mapper.
In the data mapper, map source properties, source fields, or source constants to the target Google Sheets connection, which is adding a chart. The following instructions assume that there is a source field, a source property, or a source constant that contains the value that you want to map to each target field.
If there is no source for the required value, then create a source property in the data mapper by clicking the + sign that is to the right of the Properties folder. In the Create Property dialog, give the property a name that makes it easy to map to the correct target field, for example, specify
overlayPosition
as the property name. For the property value, specify the value that you want the connection to use to create the chart. The chart’s overlay position field requires A1 notation that identifies a cell. That is, it specifies a start position but an end position specification is not required, and it is ignored if it is present. For example, specify something likeD4
as the value of the overlayPosition source property. Save the new property.When you configured the Add charts action, you might have specified a value for the spreadsheet ID, the chart title, and/or the chart subtitle. If you did, then do not map a value to that field.
To add a chart to a sheet, map source properties, source fields or source constants to target fields as follows:
Set the location of the chart by mapping an A1 notation value, which identifies a cell, to the overlayPosition target field. An end position is not required and it is ignored if present. The mapped value identifies the cell where Google Sheets places the chart. This cell is the upper left corner of the chart display.
If you do not map a value to the overlayPosition field, and you also do not map a value to the sheetId field, which identifies the sheet that you want to add the chart to, then the default behavior is that Google Sheets creates a new sheet and places the chart in A1.
You must map a value to the overlayPosition field if you plan to map a value to the sheetId field.
- Map a source spreadsheetId field to the target spreadsheetId field. The connection uses source data in this spreadsheet to add the chart to a sheet in this same spreadsheet. The source data and the chart can be in two different sheets in the same spreadsheet but they cannot be in two different spreadsheets.
- Set a chart title by mapping to the target title field. The connection inserts this string as the title of the chart.
- Set a chart subtitle by mapping to the target subtitle field. The connection inserts this string as a chart subtitle.
Identify the sheet to add the chart to by mapping an integer to the sheetId field.
If you map a value to the sheetId field then you must also map a value to the overlayPosition field.
If you do not map a value to the sheetId field and you also do not map a value to the overlayPosition field, then the connection creates a new sheet and adds the chart to the new sheet.
If you do not map a value to the sheetId field but you do map a value to the overlayPosition field, then the connection places the chart on the sheet identified by the sourceSheetId field. This is the sheet that provides the data for the chart.
0
identifies the first sheet in the spreadsheet. For subsequent sheets, the sheet ID is at the end of the URL when the sheet is visible in a browser. For example, at the end of the following URL, you can seegid=206589330
:https://docs.google.com/spreadsheets/d/1pa…ngQbKkM/edit#gid=206589330
This indicates that the sheet ID is
206589330
and that is the value that you would map to sheetId.Identify the sheet that provides the data for the chart by mapping its integer sheet ID to the target sourceSheetId field. The default is
0
, which identifies the first sheet in the spreadsheet.You can add a basic chart or a pie chart. Follow one of the following sets of instructions according to the kind of chart that you want to add.
To add basic chart, in the target panel, expand the basicChart folder and map source fields to target fields that are in the basicChart folder:
-
Set the chart type (
BAR
,LINE
,AREA
, orCOLUMN
) by mapping to the target type field. The default isCOLUMN
. - Set the title for the bottom axis of the chart by mapping to the target axisTitleBottom field.
- Set the title for the left axis of the chart by mapping to the target axisTitleLeft field.
-
Set the high level category of information that the chart shows by mapping to the target domainRange field. This field uses the Google Sheets A1 notation. For example, suppose that the first column in a source sheet provides a list of 5 products in
A2
throughA6
, and you want the chart to provide some data for each product. In this column, the first cell (A1
) must contain a label, such asProducts
. The setting of domainRange would beA1:A6
. The chart will contain the data that is inA2
throughA6
. The chart does not display the label. -
Identify the location of the source data that the chart shows by mapping to the target dataRange field. This field uses the Google Sheets A1 notation. A chart can provide only one series of data. Continuing with the domainRange example, suppose that the second column in a source sheet shows the number sold for each of the 5 products that are listed in the first column and this is the data that you want the chart to show. In this column, the first cell (
B1
) must contain a label, such asNumber Sold
. The setting of dataRange would beB1:B6
. The chart will contain the data that is inB2
throughB6
. The chart does not display the label.
-
Set the chart type (
To add a pie chart, in the target panel, expand the pieChart folder and map source fields to target fields that are in the pieChart folder:
-
Set the location of the pie chart legend by mapping to the target legendPosition field. The default is
LEFT_LEGEND
. The value must beBOTTOM_LEGEND
,LEFT_LEGEND
,RIGHT_LEGEND
,TOP_LEGEND
, orNO_LEGEND
. -
Set the high level category of information that the pie chart shows by mapping to the target domainRange field. This field uses the Google Sheets A1 notation. For example, suppose that the first column in a source sheet provides a list of 5 products in
A2
throughA6
, and you want the chart to provide some data for each product. In this column, the first cell (A1
) must contain a label, such asProducts
. The setting of domainRange would beA1:A6
. The chart will contain the data that is inA2
throughA6
. The chart does not display the label. -
Identify the location of the source data that the chart shows by mapping to the target dataRange field. This field uses the Google Sheets A1 notation. A chart can provide only one series of data. Continuing with the domainRange example, suppose that the second column in a source sheet shows the number sold for each of the 5 products that are listed in the first column and this is the data that you want the chart to show. In this column, the first cell (
B1
) must contain a label, such asNumber Sold
. The setting of dataRange would be B1:B6. The chart will contain the data that is inB2
throughB6
. The chart does not display the label.
-
Set the location of the pie chart legend by mapping to the target legendPosition field. The default is
- In the upper right, click Done to add the data mapper step.
8.4.9. Adding a pivot table to a sheet
In the middle of a flow, or to finish a simple integration, you can add a pivot table to a Google Sheets spreadsheet. A pivot table lets you aggregate, sort, or apply a function to spreadsheet data and display the results in the same spreadsheet. To add a pivot table, add a Google Sheets connection to the middle of a flow or as a simple integration’s finish connection. Then add a data mapping step before the connection. In the data mapping step, you set options that determine the location, properties, and content of the pivot table.
In this release, the Add pivot table action is limited to defining one value group, one row pivot group, and one column pivot group. Support for multiple groups in a single action is expected to be added in a future release. As a workaround, you can add multiple Google Sheets connections that add pivot tables based on the same source spreadsheet.
Prerequisites
- You created a Google Sheets connection that is authorized to access the Google spreadsheet that you want to add a pivot table to.
- You have access to the ID for the spreadsheet that contains the source data for the pivot table.
- You are creating or editing a flow and Fuse Online is prompting you to choose a step. Or, Fuse Online is prompting you to choose the finish connection for a simple integration.
Procedure
- Click a Google Sheets connection that is authorized to access the spreadsheet that you want to add the pivot table to.
- On the Choose an Action page, click Add pivot tables.
To configure the Add pivot tables action, in the SpreadsheetId field, do one of the following:
- Enter the ID of the spreadsheet that you want to add a pivot table to.
- Leave the field blank. In the data mapper step that you will add just before this connection, you will map the spreadsheet ID from a previous connection to this connection. The previous connection must access the spreadsheet that you want to add a pivot table to in this connection.
- Click Done to add the connection to the flow. The connection appears in the integration visualization flow in the location where you added it.
- If you want to add any other connections to this flow, add them now. Then return to these instructions.
- In the integration visualization flow, click the plus sign that is just before the connection that adds a pivot table to a sheet.
Click Data Mapper.
In the data mapper, map source properties, source fields, or source constants to the target Google Sheets connection, which is adding a pivot table. The following instructions assume that there is a source field, source property, or source constant that contains the value that you want to map to each target field.
If there is no source for the required value, then create a source property in the data mapper by clicking the + sign that is to the right of the Properties folder. In the Create Property dialog, give the property a name that makes it easy to map to the correct target field, for example, specify
valueLayout
as the property name. Specify the value that you want the connection to use to create the pivot table. The pivot table’s value layout field requires a value ofHORIZONTAL
orVERTICAL
so you might specifyVERTICAL
as the value of the valueLayout property. Save the new property.In the data mapper, configure a new pivot table as follows:
- Map a source spreadsheetId field to the target spreadsheetId field. The connection uses source data in this spreadsheet to add the pivot table to a sheet in this same spreadsheet. The source data and the pivot table can be in two different sheets in the same spreadsheet but they cannot be in two different spreadsheets.
Identify the sheet to add the pivot table to by mapping an integer to the sheetId field. The default is
0
, which identifies the first sheet in the spreadsheet. For subsequent sheets, the sheet ID is at the end of the URL when the sheet is visible in a browser. For example, at the end of the following URL, you cangid=206589330
:https://docs.google.com/spreadsheets/d/1pa…ngQbKkM/edit#gid=206589330
.This indicates that the sheet ID is
206589330
and that is the value that you would map to sheetId.-
Identify the sheet that provides the data for generating the pivot table by mapping its integer sheet ID to the target sourceSheetId field. If you do not map a value to sourceSheetId, the connection uses the sheetId value, or
0
if no value is mapped to sheetId.0
identifies the first sheet in the spreadsheet. - Set the source data range for generating the pivot table by mapping an A1 notation value to the target sourceRange field. For example, A2:D5.
-
Set the value layout to be used on the pivot table by mapping to the target valueLayout field. The value must be
VERTICAL
orHORIZONTAL
, which is the default.HORIZONTAL
specifies that values are laid out as columns. - Set the top left cell of the pivot table by mapping an A1 notation cell coordinate to the target start field. When the connection adds the pivot table to the sheet that contains the source data, if you do not map a value to the start field, the default is that the top left cell of the pivot table is the cell that is in the first row of the source data range and in the first empty column after the source data range. When the source data is in one sheet and the pivot table will be in another sheet, the default is A1.
To add a value group, expand valueGroups in the target panel, and map values to the fields under valueGroups:
- Map a value group name to the target name field.
-
Map a function, which you want to apply to the value group, to the target function field. The value must be
SUM
,COUNT
,AVERAGE
,MAX
,MIN
, orCUSTOM
. The default isSUM
. MapCUSTOM
to function when you are defining a formula. -
Map a custom formula, which you want to apply to the value group, to the target formula field if you mapped the
CUSTOM
value to the function field. - Map a column name, which is the coordinate that builds the value group, to the target sourceColumn field.
To add a row pivot group, expand columnGroups in the target panel, and map values to the fields under columnGroups:
- Map a row pivot group name to the target label field.
-
Map the sort order that you want to apply to the row pivot group to the target sortOrder field. The value must be
ASCENDING
orDESCENDING
. The default isASCENDING
. -
Map
true
orfalse
to the target showTotals field. The default istrue
, which enables the display of totals for the row pivot group. - Map a column name, which is the coordinate that builds the row pivot group, to the target sourceColumn field.
To add a column pivot group, expand rowGroups in the target panel, and map values to the fields under rowGroups:
- Map a column pivot group name to the target label field.
-
Map the sort order that you want to apply to the column pivot group to the target sortOrder field. The value must be
ASCENDING
orDESCENDING
. The default isASCENDING
. -
Map
true
orfalse
to the target showTotals field. The default istrue
, which enables the display of totals for the column pivot group. - Map a column name, which is the coordinate that builds the column pivot group, to the target sourceColumn field.
- In the upper right, click Done to add the data mapper step.
Additional resource