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’.
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 SHOULDuse 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.
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.
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’.
This will take you to the final overview window.
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.
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:
Depending upon your dataset and current spreadsheets in Google Sheets, this will dictate 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).
Similar to before once you have created a new spreadsheet or updated an existing spreadsheet it will give you a confirmation window.
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.
"Time out error with the ‘Developer Login"
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"
Please re-check you have the right credentials. This is a Google error not an Alteryx Error.
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'.
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.
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.
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:
Alteryx install folders
If you have any issues please reach out to Alteryx Support