How to connect Google Sheets in SSIS

SSIS PowerPack API Connectors
1

Introduction

This article guides you through connecting to Google Sheets API in SSIS / ODBC.

Prerequisites

  • SSIS PowerPack: Download and install the ZappySys SSIS PowerPack from Here.

  • Spreadsheet: Create and fill a sheet on Google’s side

Use Case

When you want to load your data from a Sheet in Google to a local base with simple steps

Steps

Create/Select a Project in Google API Console:

  1. Navigate to Google API Console.

  2. Click on the Project Dropdown at the top bar and either select an existing project or create a new one by clicking CREATE PROJECT.

    image
    image744×230 20.9 KB

  3. Once the project is set, click ENABLE APIS AND SERVICES.

  4. Enable both Sheets API and Drive API by searching for them and clicking ENABLE.
    image

  5. Return to the main screen and click on the OAuth Consent Screen tab. Provide the necessary details and save.

  6. Move to the Credentials tab.

  7. Click CREATE CREDENTIALS in the top bar, choose OAuth Client ID, select Desktop App as the Application Type, and click Create to obtain your Client ID and Secret.

    image
    image871×334 38.8 KB

Connect with Google Sheet connector in SSIS:

  1. Drag and drop a Data Flow task in your SSIS package.

  2. Inside the Data Flow, add an API Source component.

  3. Create a new connection within the API Source, and choose the Google Sheet connector. Press Continue for the next step.

  4. Fill in the connector fields, including Client ID, Secret ID, and Spreadsheet ID. Note that the Sheet ID is derived from the URL. Example: https://docs.google.com/spreadsheets/d/{{sheet_id}}/edit#gid=0

    image
    image868×775 30.2 KB

  5. Generate the token and click OK to save the configuration.

  6. Select a table/endpoint and preview the data.

    image
    image888×605 45.6 KB

Considerations

Save the credentials so you do not have to search in your account every time.

Tips

Get a list of the IDs from the Spreadsheets you have in your account.

Conclusion

By following these steps, you can seamlessly connect to Google Sheets within SSIS, utilizing the ZappySys tool for efficient data retrieval and integration. If you encounter any issues or need further assistance, you can contact our support team via chat on our website or through email at support@zappysys.com.

References

You can check our connector Google Sheet article here.