Who can use this feature?
Users with a Standard, Professional, or Enterprise plan subscription, including those currently on a free trial.
Octoparse currently supports exporting data directly to Google Sheets but it can only append rows. If you want to overwrite rows too, here we introduce another tool, Mixed Analytics API Connector, to export data to Google Sheets. API Connector is a powerful, easy-to-use extension that pulls data from any API into Google Sheets. You can set up API requests and schedule the requests to export the data to your Google Sheets.
Note that you need to be a paid user of Mixed Analytics API Connector if you want to automate the whole process.
1. Download API Connector
Install the API Connector with this link:
Go to the API Connector extension in the Google Sheet:
The API Connector will show on the right part of the screen:
2. Set up API requests
We need to connect to Octoparse API to pull data to Google Sheets. 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
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"
}
Output Settings
Click on Set Current to select the currently selected cell in the Google Sheet as the destination cell
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
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.
b. Get Non-Exported Data
Add a new sheet
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.
Click Request and choose Create Request
Configure API Request
Select Custom under Application
Select Get under Method
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.)
Output Settings
Click on Cell A2
Click Set Current
Click Output options to open more options
Choose Output mode as append
Name and Save Request
Input a name for the request
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.
Close the preview
Save the request settings
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"
}
Output Settings
Click on Set Current
Click open Output options
Select append
Name and Save Request
Name the request
Click on Edit fields
Uncheck all the fields on the Preview
Click Save
Close the Preview and Save the request
You can edit the request by going to the Request tab and choosing Edit
3. Schedule the request
Schedule the request to get a token
Click on Schedule
Click on Create Trigger
Select the request that gets Access Token first
Run request every day because the token will expire in 24 hours
Name the trigger
Save the trigger
Click on Manage Trigger to add a new trigger
Schedule the request to export data
Click Create Trigger
Select the request that exports data
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.
Name the trigger and Save
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.
You can edit the triggers by going to the Schedule tab and choosing Edit.
API Connector allows 3 saved requests and 250 requests per month for free users. You can check out more details here.