Octoparse currently does not support exporting data directly to Google Sheets, which causes inconvenience for many users. We have already connected to Zapier to export data to Google Sheet, but the number of data lines is quite limited in the free plan of Zapier.

Here we introduce another tool, Mixed Analytics API Connector, to export data to Google Sheet. API Connector is a powerful, easy-to-use extension that pulls data from any API into Google Sheets. We can set up API requests and schedule the requests to export the data to your Google Sheet.

1. Download API Connector

2. Set up API requests

3. Schedule the request


1. Download API Connector

You can easily install the API Connector using this link and click on the Install button. https://workspace.google.com/marketplace/app/api_connector/95804724197

Install.png

Once the installation is complete, you can open the Google Sheet and open the extension.

open_extension.png

The API Connector will show on the right part of the screen.

extension.png

2. Set up API requests

We need to connect to Octoparse API to pull data to Google Sheet. Here we use Export non-exported data API requests.

a. Get Access Token

The first request we need to create is to get the Access Token. The token will be your key to accessing the data in your account.

  • Click on Create

Click_create.png

Configure API Request

  • Select Custom under Application

  • Select Post under Method

  • Input the request URL: https://openapi.octoparse.com/token

  • Input Content-Type in the Header Key, application/json in the Header Value

  • In the Request body, input the information below

{
    "username": "your account username",
    "password": "your account password",
    "grant_type": "password"
}
Token_request.png

Output Settings

  • Click on Set Current to select the currently selected cell in the Google Sheet as the destination cell

Output_settings.png

Name and Save Request

  • Input a name for the request to help us know what this request is for (e.g. Token)

  • Click Save

  • Click Run

Save_token.png

After clicking on Run, you will see some information being input to the Google Sheet. This is the token we need to use later for other requests.

token.png

b. Get Non-Exported Data

  • Add a new sheet

Add_new_sheet.png
  • Go to the new sheet and input the field names on the first line

Make sure the field name order is the same as your task fields.

column_names.png
  • Click Request and choose Create Request

Create_a_request.png

Configure API Request

Task ID can be found by following this tutorial: Find Your Task ID

  • In the Header, input two Key/value pairs

    • Key: Authorization, Value: Bearer +++Sheet1!A2+++

    • Key: Content-Type, Value: application/json

*Sheet!A2 is the position of the token we get from the previous request. You can check if you have pulled the token in the A2 Cell. If not, you can input the correct info.)

Get_data_request.png

Output Settings

  • Click on Cell A2

  • Click Set Current

select_current.png
  • Click Output options to open more options

  • Choose Output mode as append

output_options.png

Name and Save Request

  • Input a name for the request

Name_the_export.png
  • Click on Edit fields

  • In the Preview, uncheck the fields of data.total, data.current and requestId.

  • Click Save

These three fields are information related to the task data and request, so we don't need to pull them.

Preview.png
  • Close the preview

  • Save the request settings

save_export.png

c. Mark data as exported

This request is to mark the data we have exported via Export Non-Exported Data request as exported.

  • Click Request and choose Create Request to start a new request

Configure API Request

  • Select Custom under Application

  • Select Post under Method

  • Input the request URL https://openapi.octoparse.com/data/markexported

  • Input Key/value pairs in the Header

    • Key: Authorization, Value: Bearer +++Sheet1!A2+++

    • Key: Content-Type, Value: application/json

  • Input the task ID information into the Request body like below

{
    "taskId": "6bb3b0c8-1e1d-432f-bc42-7206567219b"
}
Mark_data_as_exported_request.png

Output Settings

  • Click on Set Current

  • Click open Output options

  • Select append

Out_put.png

Name and Save Request

  • Name the request

  • Click on Edit fields

mark_as_exported_name.png
  • Uncheck all the fields on the Preview

  • Click Save

uncheck____.png
  • Close the Preview and Save the request

You can edit the request by going to the Request tab and choosing Edit

Edit_request.png

3. Schedule the request

Schedule the request to get token

  • Click on Schedule

  • Click on Create Trigger

Trigger_create.png
  • Select the request that gets Access Token first

Select_token.png
  • Run request every day because the token will expire in 24 hours

  • Name the trigger

  • Save the trigger

Token_trigger.png
  • Click on Manage Trigger to add a new trigger

Manage_trigger.png

Schedule the request to export data

  • Click Create Trigger

  • Select the request that exports data

Get_data_trigger.png
  • Run the request at a frequency based on your task data amount and task run frequency.

For example, if your data runs every day and gets 1000 data lines each time, you can schedule the request to run every day as this request can export at most 1000 data lines per time. If your data amount is large, you can set it to run every hour.

Run_request.png
  • Name the trigger and Save

Name_trigger.png

Do the same steps to schedule Mark data as exported request. Note to select the same run frequency as the export data trigger.

Order the Triggers

  • Cursor over one trigger

  • Drag to the correct position

Make sure that the order is Get Access Token -> Get Non-Exported Data -> Mark Data as Exported. Triggers will run in order from top to bottom.

Order.png

You can edit the triggers by going to the Schedule tab and choosing Edit.

Edit_trigger.png

API Connector allows 3 saved requests and 250 requests per month for free users. You can check out more details here.

Did this answer your question?