Introduction
Need to transfer collaborative data from Google Sheets into MongoDB for analysis, backups, or system integration? With SSIS and the ZappySys SSIS PowerPack, it’s simpler than ever.
In this tutorial, you’ll learn how to extract data from Google Sheets using the API Source with the Google Sheets Connector and load it directly into MongoDB—no scripting required.
Prerequisites
- Download and install the ZappySys SSIS PowerPack.
- MongoDB database.
- Google account.
Steps
Step 1: Create or select a project in the Google API Console
-
Go to the Google API Console.
-
Click the project dropdown at the top and either select an existing project or click Create Project.
-
Once the project is created, click ENABLE APIS AND SERVICES.
-
Search for and enable Google Sheets API and Google Drive API.
-
Go to the OAuth Consent Screen tab. Fill in the required information and save.
-
Navigate to the Credentials tab.
-
Click Create Credentials then OAuth Client ID, select Desktop App, and click Create.
-
Copy your Client ID and Client Secret—you’ll use these in SSIS.
Step 2: Configure the source component in SSIS
-
In your SSIS package, drag a Data Flow Task onto the Control Flow canvas.
-
Inside the Data Flow tab, drag the API Source component.
-
Double-click the API Source, click New Connection, and choose Google Sheets Connector.
-
Authenticate using OAuth 2.0, enter your Client ID and Client Secret and select the required scopes.
-
Generate and save the token, and click Test Connection to verify access.
-
Click OK to save the connection.
-
In the API Source component, select your desired table or endpoint.
-
Enter additional parameters if needed.
-
Preview the data and click OK to finish the setup.
Step 3: Configure the MongoDB Destination
-
In the Connection Manager, right-click and choose New Connection, then select ZS-MONGODB.
-
Configure the MongoDB connection, set the host, username, password, and database.
-
Adjust the advanced settings if necessary, and then click Test Connection to verify if the connection passes.
-
Drag a MongoDB Destination component into the Data Flow and connect it to the API Source.
-
Configure the destination. In the Connection Manager tab, select your MongoDB connection.
-
In the Component Properties tab, set Action to
Insert, and choose your collection name.
-
In the Column Mappings tab, verify that fields are mapped (they will auto-map if names match).
-
Run the package and verify that the inserted data is present in MongoDB.
Step 4: Updating MongoDB Records
To update existing records in MongoDB, pass the entire document JSON in one field and the key (e.g., ID) in another.
-
Add a JSON Generator Transform between the API Source and MongoDB Destination.
-
Define the desired JSON structure in the transform. (Refer to JSON Generator Transform Documentation).
-
Right-click the ID field, then Edit and enable Include Column in the Downstream.
-
In the MongoDB Destination, set Action to
UpdateorUpdateOrInsert. -
Go to the Column Mappings tab, map the generated JSON to the document field and the ID field to the key.
-
Run the package. Matching MongoDB documents will be updated.
Conclusion
Exporting Google Sheets data into MongoDB doesn’t require scripting, custom APIs, or complex pipelines. With ZappySys API Source and MongoDB Destination, you can build powerful, low-code ETL pipelines using familiar SSIS tools. Whether you’re integrating spreadsheets from business teams or syncing external data sources, this method offers a flexible, reliable, and scalable solution.
For more advanced file, API, and cloud integrations, explore the full capabilities of the ZappySys SSIS PowerPack.
References
- ZappySys Google Sheets Connector
- ZappySys Google Sheets Connector documentation
- MongoDB Destination
- MongoDB Destination documentation
- Related blog articles
- SSIS PowerPack
Contact us
If you encounter any challenges or have specific use cases, please contact our support team via chat or ticket.