How To Output Data To Google Sheets

Google Sheets is a very popular and convenient cloud storage service for anything data. You can leverage Google Sheets flexibility using TexAu to send your data extraction results. Let's see how it works!

In Spices

In spices, you can't send data to Google Sheets, but you can extract it in CSV.

You'll find this in the "Results" menu tab on the left, then click on the spice's "result" button on the right:

Here you will be able to extract the result of your execution as a CSV file:

In Recipes

To output data to Google in recipes, it's easier.

First, add a new spice module to your recipe. Next, click on the Google Sheets icon, then select the spice called "Send Data to Google Sheet":

Now, let's create a new Google spreadsheet:

To give your sheet the proper permission:

• Click on the green "share button" at the upper right corner of the sheet.
• Set the Sheet permission as "Editor" so TexAu can access the spreadsheet and write on it.

Finally, let's add your sheet details in TexAu so we can access it:

Sheet URL:

• Add the google sheet URL

Add Columns:

• In the mapping section, enumerate the column order you want to use on the Sheet and what variable you what to output on each column. You can pick any previous variables used in the recipe and map them to a cell range.

Connected Sheets Account:

• Connect to your Google account email address to access your Sheet. You can connect multiple Gmail email addresses to TexAu. That way, TexAu will be able to write on all your connected sheets.
• You should see this green checkmark next to your email account if your account is connected.
• You can also refresh the active connection if you use multiple Google accounts to see which account is active.



How to make a google sheet public and writable (Editor Permission)

When connecting your Google account, don't forget to check the boxes below to give TexAu the correct Google Drive permissions (via the share button). That way, TexAu will gain edit access to read and write on the sheet. Otherwise, you won't be able to map or send any data to your sheets from TexAu.



When sending data to a Google Sheet, you can select any spreadsheet tab and push data on it. For this, you need to:

• First, place yourself on the sheet tab you want to write on.
• Copy the spreadsheet URL and paste it into the TexAu sheet input field.

Start Row:

• Add the start row you want to write the data. For example, add "2" as a value if you have a header so that data writing will start on row 2 after it. Of course, this is only if you intend to write data to enrich your input Sheet.

Sheet Name:

• If the Sheet you want to write on has another name than the default "Sheet1" or is not the first one of the workbook, add the Spreadsheet's name in the "Sheet Name" field. TexAu will ask you the exact name of each Sheet if you have a custom name for it. This setting is case-sensitive. Failing to do so will prevent you from writing on the Sheet.

Push Data To Existing Row:

Check "push data to existing rows" if your Sheet already had previous data on it. That way, you can enrich existing data on a Sheet with TexAu. Just map columns or assign a range of cells to enrich your data.

💡 Note that steps 4 & 6 are primarily helpful for data enrichment of an existing Sheet only.

How To Map Variables To Google Sheets

This new method allows you to map any Google sheet directly.

You will be able to:

• Sync and auto-map any existing headers present on your Sheet
• Push and write the header from TexAu to your Sheet.

Auto-map existing header (Get header from a sheet)

Let's say you have a sheet with existing data and column headers on it:

After adding your sheet URL and setting the correct permission to "Editor", click the "Configure" button under "Add Columns".

It will load the modal below and auto-map any existing column headers you have on your Sheet.

You can also click the "Reload columns" button to load the headers from the Sheet:

This will fetch the header columns present on the Sheet automatically:

Configure or modify existing headers from TexAu to Google sheets (Push headers to a sheet)

Let's say now, you don't have any existing column header on your Sheet, but you want to configure it within TexAu, then push the header back to the Sheet.

You can easily do so by giving a name to each column header (middle column below):

You can also add new columns to your Sheet:

Once you are all set and done, you can send the header and write it directly on the Sheet by hitting the "Update Headers" button:

You will be able to see it a few seconds later on your Sheet:

You can sort the columns in any order you want. For example, when you want to write data in an empty column between two existing column headers.

Finally, as explained above, you can push the headers back to the Sheet to update this new change.



How To Send Data Without Variable Mapping (Fast Method)



Another new feature allows pushing data to the Sheet without setting up any header.

It's a quick way to get all the raw data when you only want the spice results in a recipe.

The Sheet header will inherit each default variable name from each spice.

Just connect your google account, add the Sheet URL and call it a day.



How To Enrich Data From A Google Sheet

You can enrich existing data from a Google Sheet in TexAu.

For this, you need:

• input the same Google Sheet URL that you used as input
• connect your Google Drive account
• map new columns at the end of the Sheet to add additional data on existing rows
• put "start row" = 2 if you have a header in row 1
• enter the name of the Sheet
• check "push data to existing rows" to write the data on the same Sheet as the input

Data enrichment from a specific row index

Let's say you want to enrich a Google Sheet but this time, starting from row number 150 because you already have data up to row 149 on the Sheet.

Same as before, you will use the same Sheet as input and write on it.

Let's suppose that you have a header at row index 1.

To be able to write data at row index number 150, here's what you should do:

First, configure the "Send data to Google Sheet" spice and set "Start Row" to 2 like before if you have a header on the Sheet. Also, check the box "Push Data To Existing Row" if you want to write data on the Input Sheet:

Now, for the input Sheet configuration:

Check the box since you have a header on the Sheet. For the number of rows to skip, add (n-2) row as the index:

So if you want to write data above the row number n=149, this will be 148 in our example.

The explanation for this is:

• You want to write data starting at row index 150.
• It's because a header input counts as 1 row.
• Therefore, the start row output counts as 1 row.
• So you should skip 148 rows to be able to write at row number 150 (150 - 1 header row - 1 output start row = 148)

Output

If you have no header on your current sheet, then you can use the following settings to enrich your Sheet:

Input

Here TexAu will skip 149 rows to begin writing data at row 150 (n-1 in this case). You’ll have to do a little mental calculation here, nothing too hard, really 😛.

Troubleshooting

Pay Attention To The Source Range

If you want to send more than 26 variables to a Google sheet, you need to write data on more than 26 columns.

To do so, select all (CTRL-A), right-click, and insert 26 additional new columns on the left. Now you will be able to map all variables output above column Z (range AA to AZ):

Here, the number of columns goes up to column AZ. so in this case, this will fit the Sheet with the settings below (the last column is AZ):