The 'How to Guide' to Google Sheets

[JordanB]

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.

Caveats

• These connectors are available for Alteryx Designer/Server 10.5, and Alteryx Designer/ Server 11.3+

• These connectors are not installed directly with the designer. You can download them the Alteryx Analytics Gallery

• 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: 

- 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’.

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

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’. 

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

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.

• If you are bringing data in from Google Sheets you will want to use the Google Sheets Input Connector, alternatively if you are writing out will want to use the Google Sheets Output Connector. 

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’.

• 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.

• 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.

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.

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

[aknepfle]

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?

[JordanB]

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]

@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.

[JordanB]

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

[AustinTidmore]

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"?!

[aroos]

I have the same problem.

[JordanB]

Hi @aroos & @AustinTidmore

I am looking into this now! Please standby

Best,

Jordan

[Zuccatti]

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

[JordanB]

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!

Best,

Jordan

@WayneWooldridge

[JordanB]

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.

Best,

Jordan

[FernandoVS]

Hello colleagues,

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? 

Regards

Fernando

[chambke]

Hi @FernandoVS

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.

Best, 
Kevin

[BlakeFox]

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?

Best, Blake

[DanM]

@BlakeFox They are still there https://gallery.alteryx.com/#!app/Google-Sheets-Tools/5952d212a18e9e0e48a0cafe

Regards,

DanM

[alyssa_sedai]

Hello,

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.

[JordanB]

Hi @alyssa_sedai

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?

Best,

Jordan

[alyssa_sedai]

Hi @JordanB,

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".

[JordanB]

Hi @alyssa_sedai

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

Best,

Jordan

[alyssa_sedai]

Hello @JordanB,

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.

[alyssa_sedai]

Hello @JordanB, @chambke, 

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.

[anxomasid]

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?

[alyssa_sedai]

Hello @anxomasid,

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.

[anxomasid]

Hello @alyssa_sedai,

I have made some screenshots to show you the issue:

Thank you in advance!

[alyssa_sedai]

Hello @anxomasid,

What happens if you try to run to a different workbook and append a sheet there?

[bedwards019]

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.

<FormatSpecificOptions>
<ImportLine>7</ImportLine>
</FormatSpecificOptions>

Thanks!

[jowen]

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?

Thanks.

[BlakeFox]

@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. 

[jowen]

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? 

[jowen]

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.

Thanks.

[alyssa_sedai]

Hello @jowen,

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.

[lori]

Hi,

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?

Thanks!

[coderockride]

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. 

[JohnBarker]

Hello!

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].

• Using the Google Sheets API, I am only able to point it to the sheet I specify. 
• However, that sheet is outdated everyday, a new one is added.
• Each new file added has a name with a date time stamp, it appears as  "first_part_of_file_name_YYYY-MM-DD_00_00-00"
• I also tried without the API pointing to my local "G" drive, with no success. Using > Directory, Sort, Filter, Dynamic Input, Browse.
• With that method I get a file name called "desktop.ini".

Any help would be awesome!!

[JordanB]

@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. 

Best,

Jordan

[tstevens003]

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. 

[alyssa_sedai]

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'

[jingmu]

@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!

[JordanB]

@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.

Best,

Jordan

[jtwmoore]

Hello @JordanB 

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?

Thanks,

Jared

[shaynie]

Hi,

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.
Thanks!

[BlakeFox]

@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.

[josephschafer]

---

@JordanB wrote:

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.

---

Key piece of info here.  Thanks @JordanB 

[shreyanshrathod]

Hi @josephschafer ,

Warm Greetings!!

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.

Regards,

Shreyansh

[SWP]

Hi All,

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.

Thanks!

[arpit_tyagi]

Hi All,

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.

[Beenish_shah]

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?