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.
on 05-05-2016 03:18 PM - edited on 07-27-2021 11:41 PM by APIUserOpsDM
The ‘How to Guide' to Google Sheets
We have introduced two new connectors for Google Sheets, allowing you to download and upload data to and from Google sheets.
- 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:
- All Google Sheets contained in Folders within your Google Drive will be visible in the connector.
Step 1: Sign up/in to Google Sheets
Congrats you’re now in Google Sheets
Let’s move to Alteryx
Bringing in data from Google Sheets
Writing out to Google Sheets
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:
"The email and password you entered don’t match"
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.
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:
Alteryx loads tools in the Designer from these locations in the following order:
If you have any issues please reach out to Alteryx Support
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.
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.
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"?!
I have the same problem.
Hi @Zuccatti @aroos @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!
Hi @Zuccatti @aroos @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.
I was trying to read a google sheet from google "team drive" instead of "My Drive", but I am not able to see the content in the list that Alteryx returns to me (I am able to see only the content of "My drive"). Is this a limitation of the component "Google Sheets Input" used in Alteryx? or am I doing something wrong?
We weren't able to figure out a connection to a sheet on team drive, but I was able to create a duplicate sheet on my drive that automatically mirrors changes to a sheet on team drive, then connect Alteryx to that sheet on my drive. Best I can tell, the my drive sheet will automatically and immediately reflect changes made to the sheet on a team drive whether it's open or not.
Full formula to put in cell A1 of the sheet on my drive: =IMPORTRANGE("https://...LINK TO YOUR SHEET ON TEAM DRIVES... ","SHEET NAME!A1:AK46")
In the example above, the table of data I want to pull through is in cells A1 to AK46.
The GoogleSheets Tools download for Google Sheets Input and Google Sheets Output seems to have been removed from the Alteryx Gallery. Have they been renamed? Can someone find a live link to the most up to date version of these tools?
@BlakeFox They are still there https://gallery.alteryx.com/#!app/Google-Sheets-Tools/5952d212a18e9e0e48a0cafe
I am having this #REF! issue as well. I can get the Alteryx workflow to dump to a Google tab but I have a sheet that vlookups to the Google tab Alteryx data dumps to; however, everytime I run the Alteryx workflow the vlookups get me an #REF! error. It doesn't matter that the sheet name has stayed the same nor does it matter if instead of dropping the sheet I just append to the data dump in Alteryx. I still get the error. Any ideas of what I can do because I cannot automate this unless this gets fixed.
Can you do the vlookup within the Alteryx workflow before you push the data to Google, rather than doing that function within google sheets itself?
Unfortunately no. The report is designed to be more interactive so at the top of the report a user can select something and based on what they select all of the vlookups update.
I did what @chambke suggested where I downloaded the data into a Google doc and then used import range to move over all of the data and that works as it isn't limited in the same way the vlookups are in Google. However now the report is incredible slow. The real issue is that every time you run the workflow the vlookups break. Is there anyway to actually run the data to the Google document and not have the vlookups break every time the workflow "updates".
I have just re-created an example of looking-up on two google sheets and when I make an edit in my alteryx workflow it updates the vlookup without breaking.
Can you confirm you chose the Overwrite Drop option in the Google Sheets Output tool
I have tried the Overwrite Drop option as well as append to existing sheet. If I use the Overwrite Drop options all of the vlookups break. If I use the append to existing sheet then only the "new" data vlookups break.
So instead of using an import range which really slows down your performance I would actually use =arrayformula('Sheet that Alteryx Outputed to'!A:Z) where A:Z is the columns you want to reference. This helps with the performance; now the trade off is that Google has a cell limitation so basically what you end up doing is having a minimum of 3 sheets in your Google document.
1) The sheet Alteryx built in Google
2) A copy of the sheet Alteryx built (using the arrayformula)
3) The sheet/report that you build that references the copy of the Alteryx sheet
If you reference the sheet Alteryx builds directly every time your workflow runs (so for example if you need it to run daily) it breaks all the references because Google views it as a "new" sheet and can't handle vlookuping to something that "changed". However Google can vlookup to the array formula sheet with no issues. In ideal world this would be awesome if it could be fixed because you are guaranteed to hit Google cell limitation more quickly as you basically have 2 data dumps of the same set of data and it does slow down performance because the more formulas you put into Google the slower it is (although the arrayformula is significantly faster than trying to an import range or vlookups).
It is important to note that Google has a cell limitation for the entire workbook (although I bet you 2 know this) and not a row limitation so I strongly recommend deleting any cell you don't need/won't be using to try to not hit Google limit.
Hi @JordanB, @chambke
I have two questions:
1. How can I write down in a Google Sheet in a concrete cell; for example, write the word 'Test' in the cell A4 instead of A1 (the one that is set by default)
2. Using Google Sheets Output, "Append existing sheet" option doesn't work. The rest of them work well, but this one says that "Error transferring data: Failure when receiving data from the peer". Do you have an idea about what is going wrong?
Do you typically have firewall issues? Also is it possible that your sheet doesn't have enough columns or rows so that when you try to append it doesn't work? How many columns is this? Typically Google will just add rows but if you don't have enough columns then it errors out.
I have made some screenshots to show you the issue:
Thank you in advance!
Is there a way to specify which line the data import should start on, similar to the "Start Data Import on Line" on an Excel input? I can't find that anywhere in the configuration options. I tried manually copying over the below XML from the Excel input into the Google Sheets input, but it gets removed each time.
When trying to import a large Google Sheet (100K+ rows) I get one of the following 2 errors:
Alteryx Error: Google Sheets Input (17): Tool #145: Tool #14: Error transferring data: Transferred a partial file
I am using User credentials.
If I break the file into a much smaller number of rows it works fine. This is impractical (unless there is a way to do this automatically in Alteryx without user intervention).
Is there any way to get around this or get it to load a large file?
@jowen The error you're receiving "502 Bad Gateway" is a server error on the senders side. You're not getting this due to an Alteryx issue. I just tested pulling 100k lines from a Google input tool using the user login and it worked without issues.
Thanks @BlakeFox. What would cause the bad gateway then? I receive the above error any time between 7mins and 20mins of running the job.
The file I am using is 170K rows, with 10 columns (5 dates, 4 int, 1 text). The file size is about 5,900KB. It's stored in a team drive that I have access to (and Alteryx allows me to select).
When I copy the file and delete 100K rows it works fine.
Is there anything I can look at to get more information or anything I can try to provide more detail?
As an update to the above, I have worked around the issue (not sure if it was the best solution however it does have some advantages).
I have created 2 tables in BQ; 1 is connected to the Google Sheet directly whilst the 2nd is a table that is based on the first table.
The 2nd table was required as Alteryx gives an error when trying to read from a BQ External table (table 1 above).
Using the 2nd table in the example I gave above makes everything work without the gateway or partial file errors. Instead of trying to load the Google Sheet into BQ within Alteryx I just use table 2 in BQ as my starting point. It now means that I need to schedule things to run in BQ and then schedule the Alteryx job to run after the BQ jobs have finished (BQ job is truncating table 2 and then inserting records from table 1).
If anyone has any comments on a better design then please let me know.
WOW! But yeah running to Google docs is a pain and you have to be careful how you reference your Alteryx data dump (for example you can't v-lookup straight to it because if you do every time your workflow runs the Google doc formulas break). As of this moment I can't think of a better solution than the one you did but I will think on it.
Is there a way to use the Google sheet output tool in an analytical app? If so, do you know how to configure the connector?
Is there a way to modify the tool / underlying macros remove the 60 minute expiry on the user auth approach or refresh the expiry?
I am asking as I don't currently have a way in my organisation to get a developer API instance/key/token in our company domain.
Is there a way to have the Google Sheets API point to the most recent sheet in a Google Drive folder?
I'll need Alteryx to know that it needs to look for the one added [TODAY].
Any help would be awesome!!
@JohnBarker you could definitely do this by leveraging a batch macro to pass through the values you mentioned above into the configuration.
To understand the batch macro process I would recommend this article. You won't be doing it with a Input tool, but to your Google Sheets connector.
Is there a way to create a column with the sheet names of a google sheet input? I have a google sheet with multiple tabs that I have combined into one output, but need an additional column indicating from which sheet each row originated. I know you can output the file name using the dynamic input tool for an xls but as I used the Google Input tool not sure how to achieve something similar.
You could use the formula tool and create a column. You could do an If then statement or a Where statement depending on what database you are using. For Example
Column Name - Google Sheet
If [x] is = [y] then 'Sheet One'
Else 'Sheet Two'
@JordanB Hello, Jordan! The Google Sheet Tool is not available to download at https://gallery.alteryx.com/#!app/Google-Sheets-Tools/5952d212a18e9e0e48a0cafe
Currently, the only available choice is run and schedule. Can you help? Thank you!
@jingmu I don't see this behavior when I hit run. I get two download links.
Can you make sure you run the process and then run again.
It looks like a new version of the Google Sheets tools was released a month ago. I had our newest hire download the tools when setting up Alteryx and it appears that the login options have changed. Is it still possible to log in as a developer with the Client ID, Client Secret and Refresh Token? Is there a changelog you could provide?
Is there a way to use the googlesheets output tool to change the cell (or column) viewing properties, e.g., to "wrap" or "hide" the cell/column? We're trying completely automate the google sheet to avoid any manual editing.
@shaynie Not that I am aware of, but for your use case you might want to look into some simple google scripts to get that type of cell/column specific actions done & automated.
Hi @josephschafer ,
I have encountered a similar issue with Google sheets input tool upon scheduling a workflow.
I read the solution provided by @JordanB to copy and paste the installed .yxi file to "C:ProgramData/Alteyrx/Tools" . However, under ProgramData and Alteryx I cannot find the "Tools" folder!!!!!
Do I have to create "Tools" folder manually and then paste the Google sheets input tool ?
Requesting you to help me out here!!
Thanks in advance.
Thanks for the above help. I think I followed the steps outlined but when I submit my client ID and client secret, I then get prompted to verify the access in my browser and once I do so in Alteryx I can't see or search for spreadsheets, the fields are just blank. I'm probably doing something glaringly wrong but can't figure out what it is so any help would be much appreciated.
I am getting a very strange behaviour in my alteryx version.
After selecting google sheet, I am clicking on Next and I am not getting any screen after that which means I am not able to select Sheet1, sheet2 etc and that is why my instance is not able to read it properly.
Next buttonn is quite visible but behaving like a greyed out button. It is not responding to the clicks.
Please help me.
Hi Jordan, Thank you for the article but the new version that we use for the connector it does not show the login options as given in this article. I get the options given in the screen shot
There is option for the developer login the way u have in this article. The latest version as well just gives the optional connection token or Optional OAuth2 overrides. How do we schedule a workflow using that?