Add financial account balances for multiple accounts to a Google sheet

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

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:

Here’s the Pattern I used: 

(?<=!)[A-Za-z]+

And here’s how I set up the API Request action using a PUT HTTP Method:

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/[Spreadsheet ID]/values/[worksheet name]![ColumnID][RowID]

In the body you’d use the format of:

{
"values": [["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:


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!