This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Connecting to Google Analytics is becoming more and more popular. There are a few things you need in order to use the Google Analytics macro; a Google Account (e.g., Gmail) and authorized access to an existing Google Analytics account. This article will help you get the rest of the way.
If you observe an error message like Error: Salesforce Input (1) File " ", line 1 os.environ['PATH'] = r"... please read on to learn how to adjust the environment variables to resolve it. This can happen with any one of several Connector tools, not just the tool mentioned in the example.
Leading Zeroes Removed When Writing String Data to Google Sheets
When writing string data to Google Sheets that contain leading zeros, the leading zeros are removed after being written to Google:
Environment
Product - Alteryx Designer
All Versions
Product - Google Sheets
Diagnosis
Google automatically formats values when they are written to a cell. The same behavior occurs when manually entering data into the sheet directly. When looking at the web traffic, one can see that the following is sent to Google from Alteryx:
<batch:operation type="update"/>
<id>https://spreadsheets.google.com/feeds/cells/1YW6I6543216gsgsgsgsgD24j-hHA6ydcA/od6/private/full/R4C1</id>
<gs:cell row="4" col="1" inputValue="00004"/>
The correct value "00004" appears as the InputValue. Google then returns the following:
gs:cell [ row=3 col=2 inputValue=4 numericValue=4.0 ]
Google has interpreted "00004" as a number and automatically converted it to "4."
Solution
You can workaround this by placing an apostrophe in front of the value within Alteryx prior to writing the data:
This forces Google to interpret the data as a string and include the leading zeroes. The following is a sample formula that adjusts all rows within a field to include a leading apostrophe:
"'" + [Field1]
Additional Resources
Google Sheets Output Help Documentation
The following steps detail how to obtain a client ID, client secret, and refresh token that can be used for authentication with Google related tools.
1. Open the Google Developers' Console
2. Login with the Google account associated with the data you would like to analyze
3. Create a new project by clicking the My Project dropdown (top-left corner) and selecting Create project (top-right corner of the pop up
4. Enter a Project name of your choosing and click Create
5. If you have not already enabled the Google API you will be working with, you can do so by navigating back to the webpage we started on, the Console Dashboard, and clicking Enable API:
For Google Analytics:
Other popular APIs >> Analytics API
For Google Drive:
G Suite APIs >> Drive API
Click Enable:
6. After you've confirmed that your API is enabled you can obtain API credentials by returning to the Console and clicking on Credentials in the left-hand navigation pane next to the key icon
7. Click on the Create Credentials dropdown and select OAuth client ID:
8. Select the Web application radio button and add https://developers.google.com/oauthplayground as an Authorized redirect URI before clicking Create
9. At this stage, a pop up should appear where you can copy and save your Client ID and Client Secret
You can also find your Client ID and Client Secret by returning to the Developer's Console >> Credentials and clicking the name of the app we just created:
10. Go to https://developers.google.com/oauthplayground
11. Click on the gear icon in the top-right corner of the page and click the checkbox for Use your own OAuth credentials, enter the client ID and client secret from step 13, and click close
12. Copy/paste the respective scopes into the Input your own scopes field and click Authorize APIs
For Google Analytics
https://www.googleapis.com/auth/analytics.readonly
For Google Sheets
https://www.googleapis.com/auth/drive, https://www.googleapis.com/auth/drive.appdata, https://www.googleapis.com/auth/drive.readonly, etc
14. Click Allow
15. Click Exchange authorization code for tokens and save the Refresh token
16. Test the authorization by sending a request for an available operation from List possible operations
17. If successful, the client ID, client secret, and refresh token that you obtained in the prior steps can now be used for authentication with the Google related tools