Just sharing this instructions in case it's useful for anyone else.

This should allow you to export directly from Gainsight to a spreadsheet using the Google Sheets API and external actions.

Once you have it set up you can schedule the rules as always and at least you should be able to avoid using the S3 bucket for this type of export. 

Some considerations:

• There seems to be an intermittent issue with a call being lost on Gainsight’s side and support is investigating. So TEST IT A LOT! I will update when they find/fix the problem. I’ve tried this exact solution using POSTMAN and it works great. 
• These instructions only show how to configure an Append to a spreadsheet, but you should be able to configure almost anything (like clearing the spreadsheet and exporting all values again, or updating only a specific range of cells)
• Each external action only allows access to one specific spreadsheet and sheet (for now)
• You need to have the GSheets API enabled and set up in the Google Cloud Console. If you do not have access to that bc your company does not allow it, there is a section that tells you the info you need to provide to your IT Team so they can configure it for you.
• You need to have access with your user to the spreadsheet you want to edit.

If you have any questions, improvements or find any errors let me know and I’ll change it!

Prerequisites

Google cloud access + permissions

Project creation and configuration or asking your IT department to create it for you. You’l find the required information for the IT request below. 

Sharing permissions to the spreadsheet

You must have sharing permissions set to be able to edit the spreadsheet for those users defined as test users in the Google Cloud Project. 

Limitations

1 external action ⇔1 spreadsheet and range

This is a simple configuration and will be improved going forward. For now and due to Gainsight’s limitations on dynamic values for URLs, it will only allow the connection of one external action to one spreadsheet and range. If you need to access another spreadsheet you will need to configure a second external action.

Google Cloud project

Link to google cloud: console.cloud.google.com

How to request to your IT department

You will need either of these two options:

1. Access to Google cloud project with the following permissions:

   1. Creation of a project linked to a billing account(nothing will be charged since it the service to be used is free) and saved within an organization’s folder(you will need permission to create a new folder or access an existing one)

   2. Creation of credentials

2. IT department creates a project with credentials for you with the following configurations:

   Scope: https://www.googleapis.com/auth/spreadsheets

   1. Google sheets API enabled

   2. Redirect URL: The redirect URL for your specific instance can be found within the custom connector configuration and you can copy it. 

                   e.g: https://gateway.<your instance            subdomain>.gainsightcloud.com/v1/api/connector/connection/oauth/accesstoken

4. Application type: Web Application

5. Authorized JavaScript origins: https://<your instance subdomain>.gainsightcloud.com/ 

6. Make User Type Internal

7. Add Test Users: <your gainsight user> (this will be used to authenticate the connection)

8. They will need to provide you with the CLIENT ID and CLIENT SECRET created with the credentials. You will use them to configure the connector. 

How to create your own project

New Project

1. Open dropdown list from the top

2. Select “New Project” from the top right.

3. After creating the project, you will see it selected on the dropdown list on the top. Any configurations done while it is selected will be done within that project. 

Activate Sheets API

4. In the navigation menu on the left,search for API & Services

5. Click on Enable APIS and Services

6. Search for the Google Sheets API and enable it

Credentials and service account

1. After it is enable it will request that you to create credentials

It will request to create a service account (can be used to give access to virtual machines instead of specific users) WE WILL NOT USE IT but it needs to be created. After you populate the service account name, it will automatically populate the service account id. Press CREATE AND CONTINUE and then DONE.

2. Complete the information for Credential Type:

   Are you planning to… → No,I’m not using them

   1. Select an API→Google sheets API

   2. What data will you be accessing: → Application data

   3. Press NEXT

OAuth Consent Screen

Step 1- OAuth consent screen

1. Select OAuth consent screen from the navigation menu

2. Choose User Type → Internal

3. Populate the required fields

   1. APP Name

   2. User support email

4. Click on SAVE AND CONTINUE

Step2 - Scopes

1. Click Add or Remove Scopes

2. Search for “sheets”, check the google sheets API scope and click on UPDATE.

3. Check that the scope was added to “Your Sensitive Scopes”

Step 3 - Test Users and 4 - Summary

4. Add your gainsight login user to the test users and press SAVE AND CONTINUE

5. Press back to dashboard

Create OAuth2.0 client ID

1. Select Credentials in the Navigation menu and then select Create Credentials

2.  Choose OAuth client ID

3. Select Web Application from the drop down menu

4. Add the name of this client. (e.g: gainsight)

5. Add your instance URL under Authorized Javascript origins: https://<your instance subdomain>.gainsightcloud.com/ 

6. Add your instance’s redirect URL under Authorized redirect URLs:

Where to find it in Gainsight? → Create a connector in the Connector 2.0 menu, and copy the redirect URL.

7. PRESS CREATE after adding it to the credential page.

8. The details of your credential will appear in a pop up. Copy the Client ID and the Client secret. You will need them to create the custom connector in Gainsight. 

GS Custom Connector

Custom Connector 2.0 configuration

The next steps will all be done in Gainsight

1. Within the Connectors 2.0 menu, press CREATE CONNECTION and populate the following fields:

   1. select Custom Connector

   2. Name your connection

   3. Content Type → application/json

   4. Authorization URL → https://accounts.google.com/o/oauth2/auth

   5. Access Token URL → https://oauth2.googleapis.com/token

   6. Client ID → <your Google project credential client ID>

   7. Client Secret → <your Google project credential client secret>

   8. Scope → https://www.googleapis.com/auth/spreadsheets

2. Click on AUTHORIZE. A new window will open requesting permissions and your user login. When you allow, then the connection will be done and you should be able to use external actions with this connector. 

External Action

• Once you have the connector working, it’s time to configure specific external actions for this connector to be accessed by the rules engine. 

• Due to the type of configuration available within the external actions,these instructions mean that each external action is limited to one spreadsheet and one range of cells within that spreadsheet. 

• In this example we will use the google sheets method append. The input range is used to search for existing data and find a "table" within that range. Values will be appended to the next row of the table, starting with the first column of the table

• More API methods can be found in the GSheets API documentation

• We will use the valuInputOption=RAW → this tells the GSheets API to interpret only raw data, nothing you write will be interpreted as a formula. If you choose instead “USER_ENTERED” you can send “=A1+A2” and it will interpret it as a formula instead of a string.

URL

• Call Method → POST
• URL template→ https://sheets.googleapis.com/v4/spreadsheets/<spreadsheetID>/values/<range>:append?valueInputOption=RAW

          <range> → needs to be written in A1 notation.

          e.g: Sheet1 (this will allow to append values to any part of sheet1)

                 Sheet1:A1:B15 (this will only allow within the A1:B15 range of sheet 1)

         <spreadsheetID> → can be found in the browser url when you open the spreadsheet.

Payload

• We will define 14 possible fields to upload, each field will be a column in a row since each call of an external action will be done for each record in the dataset defined in the rules engine. 

• Fields can be left empty and no errors will be generated. 

• Use the plain option and paste the following code: 

{          

                        "values": on"{{Field1}}",

                                           "{{Field2}}",

                                           "{{Field3}}",

                                          "{{Field4}}",

                                          "{{Field5}}",

                                          "{{Field6}}",

                                          "{{Field7}}",

                                          "{{Field8}}",

                                          "{{Field9}}",

                                          "{{Field10}}",

                                          "{{Field11}}",

                                          "{{Field12}}",

                                          "{{Field13}}",

                                          "{{Field14}}"]],

                         "majorDimension": "ROWS"

}

Rules engine

Remember that each action defined will be applied to one record, so each record will make a call to the GSheets API and append one ROW to the spreadsheet.

Append action example

1. Within the rules engine, you must first define the dataset as always and in the “Action Setup” step, you will have to choose Add→Call External API

2. Choose the connector for the Google sheets API and the external action you configured.

3. The field mapping you configured in the payload will appear and you can choose source fields or a Custom Static Value

​

Have fun and test a lot