Octoparse supports exporting data to Google Sheets manually or automatically.
There are two methods to connect to Google Sheets:
1. OAuth: Authorize your Google account to Octoparse directly. This method does not require long setup steps and will create a new Google Sheet in your Google Drive.
2. Service Account: Use a Google service account to connect to Octoparse. This method will require more complicated steps to create the service account but will help protect your Google account.
1. Oauth: Authorize your Google account to Octoparse (Recommended)
This method is only available in Version 8.7.4. Please download it at: https://www.octoparse.com/download
Step 1. Select Google Sheets when exporting data
Step 2. Input a Configuration name to save the settings for future usage
Step 3. Select Authentication as OAuth and choose Authorize new account
Step 4. Follow the instructions to authorize your Google account to Octoparse
Step 5. Add a SpreadSheet
Input a name to create a new spreadsheet.
Note: Octoparse can only create a new spreadsheet with the authorization and access the spreadsheets it creates. Other spreadsheets cannot be accessed.
Step 6. Select a sheet
Step 7. Click Export
2. Service Account: Create a Google service account to connect to Octoparse
To enable data export to Google Sheets in Octoparse, users must finish some preparatory work in their Google account first.
Step 1: Create a new project in Google Cloud Console
Go to Google Cloud and create a new project for Google Sheets.
Click Select a project (or an existing project's name if you have other projects) and select NEW PROJECT from the popup window.
Name the project and click CREATE to continue
Step 2: Enable Google Sheets API from API Library
Select ENABLE to finish the step.
Step 3: Create a service account in Google Cloud and download the .json file
Go to Service Accounts in the IAM&Admin settings and click +CREATE SERVICE ACCOUNT
Enter a Service account ID and click DONE to finish the setup. All the Other settings are optional.
You will find an email address appearing under your current project, that's your Client Email. Click on it to go to check the details.
After you open the details page, switch to KEYS and click ADD KEY.
Click Create new key
Click CREATE to generate a JSON file and download it to your device.
You will find a .json file downloaded to your device. Service account keys could pose a security risk if compromised. Please keep them in a safe place on your local device.
Step 4: Grant Google Sheets access to the Service Account
Go to the target Google Sheets and share it with your Service Account mail by adding it as an Editor.
Congratulations! You've finished all of the preparation work on the Google Account side.
Step 5. Input the information into Octoparse after choosing the ServiceAccount
Enter the target Google Sheets URL and type in the Client Email and Private Key found in the .json file we downloaded earlier. You can test the setup by clicking Test Connection.
Note: The Private key should include the "-----BEGIN PRIVATE KEY-----" and
"\n-----END PRIVATE KEY-----\n"
Step 6. Click Export
Note: Please make sure the spreadsheet has enough columns to host the data if you have added many data fields in your Octoparse task.
Export cloud data automatically
With Octoparse version 8.6 or above, we can export the Cloud data to Google Sheets automatically.
Click on Task Settings -> Automation -> Export to database -> Edit
Input a name in the Auto-export plans as the name for this auto-export project
Select either one of the Authentication methods and input the required information
Set up export intervals as needed
Click on Test Connection to see if the connection works
Click on Confirm to save the settings
After the setup, the Auto-export tool will pop up with the Auto-export task enabled.
Note:
Starting from version 8.6.8, Auto-export to Google Sheets does not require the scheduler tool to be opened. You can close your device and enjoy seamless exports. (Exporting to MySQL or SQL databases still requires the scheduler to be opened.)
Auto-export data to Google Sheets will only export non-exported data.
Data will be expanded to the sheet. If you want to overwrite the data every time exporting, you can try the method mentioned in this video to delete the rows automatically: https://www.youtube.com/watch?v=Z5rfqW1K3LI
Additional ways to automatically export data to Google Sheets
Octoparse currently supports the following methods to automatically export data to Google Sheets:
Via Zapier: How To Export Data to Google Sheets via Zapier
Via Octoparse OpenAPI: check the documentation and sample codes on Github
Via Mixed Analytics API Connector (Google Sheets extension tool): Exporting Data to Google Sheets with Mixed Analytics API Connector