IN THIS SECTION

• Overview
• Getting Started
• Trigger Step
• Zapier Code Step
• Google Sheets Step
• Troubleshooting
  • Polling
  • Running Zap Manually

Overview

This article explains how to use the Joiin Zapier App to set up a workflow to automatically pull data, on a regular basis, from the Joiin Trial Balance report into a Google Sheets document.

Getting Started

1. Go to Zapier (https://zapier.com/app/zaps) 

2.  Click on '+ Create -> New Zap

Trigger Step

The first Step in the Zapier Workflow is the Trigger Step. Follow these instructions to set this up.

1.  Click on Trigger and search for Joiin - select Joiin

NOTE: This is a POLLING trigger. So will poll at intervals to see if there is any new data in Joiin. If new/updated data is detected, the Zap will run. The polling interval can be set in the Zap settings.

2. Setup the Trigger Event to Trial Balance Updated

3. Select or connect to the Joiin Account

Select a previously connected Joiin account or connect to a new one. To connect to a new one, click on + Connect a new account and enter the Joiin API Key.

To obtain an API Key, go to https://app.joiin.co/user/api-access to generate the API key (when you generate the Key you may need to wait 90 seconds for it to be active).

Copy the key and enter it into Zapier.

4. Continue to Configure step

Companies - select the companies you wish to include in the report

Currency - select the currency of the report output

Year - select the year of the report

CSV File Output - select FALSE

5. Continue to Test

Click on Test Trigger (you may need to click “Test trigger” twice) and a test record will be retrieved.

Click on this record and click Continue with selected record.

Zapier Code Step

The Code Step is used to transform the data received from Joiin into a format that can be used by the Google Sheets Zapier app.

1. Search for "code" in the Zapier App browser and select the Code app.

2. For Action Event select Run Javascript

3. Click Continue to Configure step

4. In Input Data enter "inputData" and select Raw Output data field

5. Paste the following code into the Code box

const data = JSON.parse(inputData.inputData);
const output2 = data.accounts.map((row,i) => ({
  'Row ID': i+2,
  name: row.displayName,
  jan: row.Jan,
  feb: row.Feb,
  mar: row.Mar,
  apr: row.Apr,
  may: row.May,
  jun: row.Jun,
  jul: row.Jul,
  aug: row.Aug,
  sep: row.Sep,
  oct: row.Oct,
  nov: row.Nov,
  dec: row.Dec,
}));
return output2;

6. Click Continue to Test 

Click on Test step to test the step. You will see and example of a row with properties such as Row ID, Name, Jan set, e.g.

Google Sheets Step

The Google Sheets step uses the Google Sheets Zapier App to update a spreadsheet with the results from your Joiin Trial Balance.

1. Prepare your spreadsheet.

First you must create a Google Sheet to receive the data. The sheet must be in the following format.

NOTE: You can change column header names as required but the names will appear differently than in the mapping instructions below. 

You can also add additional columns (such as Total or % Change) as long as the header names can be used to identify which columns to be populated.

You can also add additional header or footer rows but you may need to adjust the Row ID parameter set above.

2. Add the Zapier Google Sheets App

Click on the Add icon below the Code step and search for Google Sheets and select this app.

3. For Action event select Updates Spreadsheet Row(s)

4. For Account select the Google Account you wish to connect to.

5. Click Continue to Configure step

6. Select the Drive, Spreadsheet and Worksheet corresponding to the sheet you created above.

7. Map the rows

A field will be provided for each of the column headings in the spreadsheet. In each field, select the corresponding field from the output of step 2.

Press forward slash to bring up the list of fields - select from the list in Step 2 - e.g. as below, map the Name field from Step 2 to the Name field in the spreadsheet.

Repeat for all fields.

8. Click Continue to go to Test step

9. Click on Test step to test. You will see your test record appear in your Google Sheet.

10. Now you can Publish your Zap and your sheet will be updated as and when the Trial Balance data in Joiin changes.

Troubleshooting

Polling

Once published, your Zap should poll on a regular basis to see if Trial Balance data has changed in Joiin. If Zapier detects that data has changed, the Zap will be run. You will only see a run in the Zap History if the Zap runs, not every time polling occurs.

The polling interval is shown in the Joiin App step. You can change the Polling Interval by clicking on it - or by opening the Zap Advanced Settings in the left-hand toolbar.

Running Zap Manually

If your Zap doesn't seem to be updating the spreadsheet - you can run it by going into the Zap and clicking Run at the top right.

You will get a message indicating if new data has been found or not, e.g.