cancel
Showing results for 
Search instead for 
Did you mean: 

The 'How to Guide' to Google Sheets

Alteryx
Alteryx

The ‘How to Guide' to Google Sheets

 

In Alteryx 10.5 we have introduced two new connectors for Google Sheets, allowing you to download and upload data to and from Google sheets.

 

Caveats

 

  • These connectors are only available in Alteryx Designer/Server 10.5. You can download 10.5 here.
  • These connectors are querying the Google Sheets API, which is different from the Google Drive API.

- If you have Excel, .csv, .txt or.ods file types in your Google Drive, you will have to convert them to a Google Sheets file before pulling them in.

- Google Sheets files that can be read by the connector will have this icon: sheets.jpg

- All Google Sheets contained in Folders within your Google Drive will be visible in the connector.

 

Step 1: Sign up/in to Google Sheets

 

  • To use these connectors you will need a Gmail account linked to your Google Sheets Account. Follow this link and click ‘Go to Google Sheets’.

Pic 1.png

 

  • This will then take you to the Google Account login. Enter your details here or sign up for an Google Account if you do not have one.

 

Pic3.png

 

 

 

Congrats you’re now in Google Sheets

 

  • Within Google Sheets you will be able to see any excel documents you have uploaded previously or if this a new account you will not see any documents other than ‘Start a new spreadsheet’. 

 

Pic 3.png

 

 

  • If you do have spreadsheets exist you will see them posted below the 'Start a new spreadsheet' section.

Pic 4.png

 

 

Let’s move to Alteryx

 

  • We now have Google Sheets configured and have the correct credentials to connect.

 

  • Depending upon if you want to bring files into Alteryx from Google Sheets or write data to Google Sheets, this will dictate the type of connector you will use.

 

 

Bringing in data from Google Sheets

 

  • When you bring in the Google Sheets Input connector you will see two sign in options, one for ‘User Login’ and another for ‘Developer Login’.

 

original.png

 

 

  • The Developer login will require you to set up Google API Credentials. This option requires obtaining a Client ID, Client Secret, and Refresh Token. You SHOULD use this option if you are looking to schedule this tool as it does not require re-entering credentials to run a workflow.

 

  • The ‘User Login’ is appropriate for all other scenarios but you will need to re-enter your credentials every 60 minutes or when you close and re-open a workflow.

 

  • Once you hit ‘User Login’ it will generate a pop-out window with the same screen as the Google Accounts Login. Enter your username and password here and hit accept.

 

  • This will now take you back to the Google Sheets Input connector configuration window in the designer.

 

  • You will now see the spreadsheets which exist on Google Sheets.

 

Pic 6.png

 

 

  • You can now select one of the sheets and it will highlight in blue and populate where it says ‘no value selected’ above.

 

  • Once this sheet is selected the next button will become active and you can press it.

Pic 7.png

 

 

  • If there is a sheet associated with the spreadsheet it will then prompt you for that on the next window.

 

  • There is the tick box for ‘First row contains field names’. When this box is checked, Alteryx sets the first row of input as field header information. If you leave this option unchecked, Alteryx assigns field names based on their order (e.g., 0, 1, 2).

 

  • Once you select the sheet it will then become active and you can press ‘Done’.

Pic 8.png

 

 

 

  • This will  take you to the final overview window.

 

original-(1).png

 

 

  • This overview window will give you a chance to ‘change spreadsheet’ if you would like. If you are happy with your selection you can run the workflow and it will give you the data from that Spreadsheet.

Pic 10.png

 

 

Writing out to Google Sheets

 

  • Similar to the input tool the Google Sheets Output connector will ask you for your Google Account in a pop out window.

 

  • Once you have logged in it will give you five different options:

original-(2).png

 

  • Depending upon your dataset and current spreasheets in Google Sheets this will disctate what option you choose.

 

  • With all other options other than ‘Create New sheets’ it will populate with spreadsheets and sheets which already exist in your Google Sheets account. If you choose ‘Create New Spreadsheet’ this will prompt you for a new Spreadsheet and Sheet name (see below).

original-(3).png

 

  • Similar to before once you have created a new spreadsheet or updated an existing spreadsheet it will give you a confirmation window.

original-(4).png

 

 

  • If you are happy with the configuration window you can now run the workflow and it will be written to your Google Sheets Account.

 

Congrats you have successfully configured the Google Sheets Input & Output connectors!!!

 

Tips & Tricks

If you are scheduling a workflow with a Google Sheets connector we recommend using the ‘Developer Login’. This will use your hard coded Google API credentials and therefore will not expire every 60 minutes. 

 

If you wish to Change Credentials please use the hyperlink in both connectors and it will return you back to the Google Account Login page.

 

Pic 16.png

Common Errors

 

"Time out error with the ‘Developer Login"

 

Solution 

The Developer Login authentication method uses the Google Spreadsheets API and may time out when trying to download large files. If you encounter this issue:

 

  • Break one large sheet into two or more smaller sheets.
  • Use a Google Sheets Input tool for each sheet and combine the results with the Union tool.

 

 

"The email and password you entered don’t match"

 

Solution

  • Please re-check you have the right credentials. This is a Google error not an Alteryx Error.

 

Error credentials.png

 

 

 

Common Issues

If you have version 1 of the Google Sheets input connector, when you read in data it will change your data types in the Google Sheets Spreadsheet to 'Plain Text'. 

 

Solution

This has been fixed in the latest version of the Google Sheets Connectors. Please re-download the connectors. 

 

'Cannot find Macro' - When scheduling the Google Sheets Tools. 

 

Solution

So the reason is because the yxi package gets installed into a different location on the server and the user needs to manually save this is a new folder.

 

The Server documentation refers to this on page 57. In short here is the summary of what the user has to do below:

 

When a tool that is packaged as a .yxi file is installed, the tool is added to the tool palette in Designer and the .yxi file and supporting macros are installed in the AppData\Roaming folder in the user’s account. For example: C:\Users\\AppData\Roaming\Alteryx\Tools\.

A .yxi file must be manually installed before a workflow that includes that tool can be run.

 

In order for a tool package to be available to Gallery and Scheduler processes on a machine, a Gallery administrator must copy the contents of the .yxi file to the C:\ProgramData\Alteryx\Tools directory on the server.

In a multi-node environment, the .yxi file must be added to every worker machine in your server deployment.

To install a Tool Package on the server:

  • Double-click the .yxi file and select OK on the Tool Installer window.
  • Locate the user Tools directory: C:\Users\\AppData\Roaming\Alteryx\Tools.
  • Copy the folder of the tool to be installed on the server.
  • Paste the folder in the following directory: C:\ProgramData\Alteryx\Tools.

 

Alteryx loads tools in the Designer from these locations in the following order:

  • C:\Users\\AppData\Roaming\Alteryx\Tools
  • C:\ProgramData\Alteryx\Tools
  • Alteryx install folders

 

If you have any issues please reach out to Alteryx Support

 

Best,

 

Jordan Barker

Solutions Consultant

Comments
aknepfle
Atom
I have a google sheet set up with interactive formulas on other tabs based off raw data that is written to a tab of the same sheet from alteryx. When I do this all the formulas that had the raw data tab in it say #REF after imported from alteryx. Is there a way around this?
Alteryx
Alteryx

Hi @aknepfle

 

Have you had this process work before? I am just interested because it seems many other Google sheets users have had this issue independent of feeding raw data from Alteryx. 

 

Are you using IMPORTRANGE?

 

There are a few workarounds to Google Sheets which might be worth a try.

 

Best,

 

Jordan Barker

Solutions Consultant  

 

 

aknepfle
Atom

@JordanB

I havent had this work before. i was using an excel export but wanted to try the google sheets just for the benifits it offers with sharing. When using the IMPORTRANGE() it is either too much data for it to handle at once or when seperating the formulas slows the google sheet down so much that it will not work.

Alteryx
Alteryx

@aknepfle did you try the other workarounds suggested in this link? 

After downloading the GoogleSheetsTool.yxi and loading into Alteryx the "User Login" option does not seem to work. Immediately upon clicking it an error from Google appears "401: The OAuth client was disabled." Maybe Google said "no mas"?!

AR
Meteor

I have the same problem.

 

oauth.png

 

Alteryx
Alteryx

Hi @AR & @AustinTidmore

 

I am looking into this now! Please standby

 

Best,

 

Jordan

Zuccatti
Alteryx Certified Partner

I'm getting the same error as @AR & @AustinTidmore.

 

 

Alteryx
Alteryx

Hi @Zuccatti @AR @AustinTidmore 

 

We believe this to be related to a google security breach on their systems. We are working with them to get this resolved ASAP. 

 

Please watch this thread for further updates!

 

Best,

 

Jordan

@WayneW

Alteryx
Alteryx

Hi @Zuccatti @AR @AustinTidmore

 

Thank you for your patience on this! The tool should be back up and running, please let me know if you run into any issues.

 

Best,

 

Jordan