Skip to main content

Hi,

I’d like to use Zapier to add financial account balances for multiple accounts to a Google sheet on a daily basis.

I can get the balances using a GET in Webhooks.

I can add a new column for my sheet using the “Create Spreadsheet Column” Zap with today’s date.

I can’t work out how to add the balance to the new column?

Here’s what my spreadsheet looks like with sample data:
 

So on a daily basis I’d like to add today’s date to E1 and then account balances to E2, E3, E4.

 

Any suggestions?  Thanks for any help!

Hi @Bridge 

I’d recommend logging each as it’s own row instead of trying to add more columns.

Fields

  1. Account
  2. Currency
  3. Account #
  4. Date

Thanks for the input @Troy Tessalone!

This is not possible unfortunately as the format is used extensively across our business and it has other connections so can’t change.


Hi @Bridge! 👋

I did some digging into Google’s API docs here and found a way to do it using an API Request action for Google Sheets! 🙂

After using a Create Spreadsheet Column action to add a new column, I had to use a Formatter (Text > Extract Pattern) action to extract the column ID value like so:
658849ef6dce7defd78d19a55a9ae839.png
Here’s the Pattern I used: 

(?<=!)dA-Za-z]+


And here’s how I set up the API Request action using a PUT HTTP Method:
faa1254918a77a18f85cb97980dadfb4.png
In the above example in the URL I manually typed in the row number (4) but you can select the relevant row from a previous Google Sheets action in your Zap to ensure it adds the value into the correct row. 

For the URL field you’ll need to use the following format: https://sheets.googleapis.com/v4/spreadsheets/pSpreadsheet ID]/values/oworksheet name]!eColumnID]>RowID]

In the body you’d use the format of:

{
"values": so"Test value"]]
}

And replace Test value with the relevant field that contains the account balance value.

The above resulted in the newly created column (J) in row 4 being updated with the desired value:
1f248e55938fac0e7d1ef42d8d717cb5.png

You’d need additional API Request actions to add the values to other rows, so it may be more efficient to use that API Request action inside a loop to have it run for varying numbers of rows. You can learn more about how to use Looping by Zapier here: Loop your Zap actions


Hope that helps to get you pointed in the right direction. If you run into any issues on that or have further questions just let us know - happy to assist further!