google sheets data to azure synapse dedicated sql pool

Vaibhav B 31 Reputation points

Hello Experts,

I need to integrate google sheets data with synapse's dedicated SQL pool. Can you please suggest to me how I can proceed with this?

1-- Using Mapping Data flow and Googles sheets connector--- Not possible in my case. I am using SHIR and mapping dataflow does not support SHIR.
2-Using Google APIs I was not able to get through with this. I have created a Service Account and OAuth client ID with the developers' console of google, but I am not able to understand how to provide these things to web activity.

Kindly provide your suggestions. or documentation. It would be very helpful
Thank you.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
3,806 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ShaikMaheer-MSFT 35,786 Reputation points Microsoft Employee

    Hi @Vaibhav B ,

    Thank you for posting this query in Microsoft Q&A Platform.

    Google sheets connector for supports in Mapping data flows only with Azure IR. Since you cannot use that, Below are few approaches which I could suggest.

    To move data to/from a data store that the Azure data factory does not support, or to transform/process data in a way that isn't supported by the service, you can create a Custom activity with your own data movement or transformation logic and use the activity in a pipeline. The custom activity runs your customized code logic on an Azure Batch pool of virtual machines. Click here to know more about this.


    You can consider writing your own code in Azure Functions which makes API calls to Google sheets and fetch data for you. Azure Functions activity can be used in Azure data factory pipeline.


    You can consider move data from google sheets to Google cloud Storage(S3 API). And then use Copy activity in Azure data factory to take data from Google Cloud storage to your destination. Click here to know about google cloud storage connector in ADF.


    you can try to use REST connector data set in Copy activity source tab. Inside REST connector dataset make API call to google sheets and fetch data.

    Hope this helps. Please let me know if any further queries. Please feel free to share your implementation details if you processed to some extent along with error details so that we can help better.


    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification