{"id":8475,"date":"2019-12-11T12:29:14","date_gmt":"2019-12-11T12:29:14","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=8475"},"modified":"2025-03-19T14:04:19","modified_gmt":"2025-03-19T14:04:19","slug":"write-update-google-sheet-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/write-update-google-sheet-ssis\/","title":{"rendered":"How to write\/update Google Sheet in SSIS"},"content":{"rendered":"

Introduction<\/span><\/h2>\n
UPDATE:<\/strong>\u00a0ZappySys has released a brand new API Connector for Google Sheets Online<\/a> which makes it much simpler to\u00a0Read\/Write Google Sheets Data in SSIS<\/strong> compared to the steps listed in this article. You can still use steps from this article but if you are new to API or want to avoid learning curve with API then use newer approach.<\/p>\n

Please visit this page to see all<\/a>\u00a0Pre-Configured ready to use API connectors which you can use in SSIS API Source<\/a> \/ SSIS API Destination<\/a> OR\u00a0API ODBC Driver<\/a> (for non-SSIS Apps such as Excel, Power BI, Informatica).<\/p>\n<\/div><\/div>\n\"\"<\/a>In this post, you will learn\u00a0How to write\/update Google Sheet in SSIS. We will use drag and drop REST API connectors from\u00a0SSIS PowerPack<\/a>. No need to download any SDK or learn programming languages\u00a0(e.g. JAVA, C#, Ruby, Python) when you use SSIS PowerPack Connectors. We will use the Google Drive API and Google Sheet API to write and update Google Spreadsheet in a few clicks (using OAuth 2.0 connection in SSIS).<\/p>\n

In this tutorial, we will use the\u00a0REST API Task<\/a>\u00a0to call some API.\u00a0We will use the\u00a0OAuth connection<\/a>\u00a0along the\u00a0REST API Task<\/a>\u00a0to write \/ update data to Google Spreadsheet.<\/p>\n

 <\/p>\n

Prerequisites<\/span><\/h2>\n

Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:<\/p>\n

    \n
  1. SSIS<\/abbr>\u00a0designer installed.\u00a0Sometimes it is referred to as\u00a0BIDS<\/abbr>\u00a0or\u00a0SSDT<\/abbr>\u00a0(download it from Microsoft site<\/a>).<\/li>\n
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\n
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a>\u00a0is installed (download it<\/a>).<\/li>\n
  4. Credentials of Google API.<\/li>\n<\/ol>\n

    Component Mention in this article<\/h2>\n
    \n\n\n\n\n\n
    \"\"<\/a><\/td>\nJSON Generator Transform<\/a><\/td>\n<\/tr>\n
    \"\"<\/a><\/td>\nWeb API Destination<\/a><\/td>\n<\/tr>\n
    \"\"<\/a><\/td>\nREST API Task<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    Create a Google API Project<\/span><\/h2>\n

    The first step to access any Google API is to create an API Project in Google Console. If you don\u2019t want to go through this then Skip this\u00a0Step-1 and in the next section select the Default OAuth App option on OAth Connection Manager (This is the easiest option for now unless you want to use your own OAuth App).<\/p>\n

    Check this article<\/a>\u00a0for step-by-step instructions.\u00a0When you follow these instructions make sure you enable Google Sheet API.<\/a><\/p>\n

    Create OAuth Connection Manager in SSIS<\/span><\/h2>\n

    Once you create a Google API project and obtained a Client ID and Client Secret your next step is to create\u00a0OAuth Connection Manager<\/a>\u00a0in SSIS. ZappySys OAuth connection manager comes with many predefined OAuth Providers (e.g. Facebook, Twitter, Google, etc) but you can also define custom OAuth settings for any OAuth enabled API.<\/p>\n

    To create SSIS OAuth 2.0 Connection for Google API perform the following steps.<\/p>\n

      \n
    1. Download and Install SSIS PowerPack.<\/li>\n
    2. Create a new SSIS Package.<\/li>\n
    3. Right-click in Connection Manager Area and Click \u201cNew Connection\u201d.<\/li>\n
    4. When prompted select ZS-OAUTH connection type,\u00a0On the OAuth Connection Manager Select Provider=Google.<\/li>\n
    5. Click here to create credentials.<\/a> Then select the \u201cUse Custom OAuth App\u201d option and enter your ClientID, ClientSecret.<\/li>\n
    6. In the Scopes\u00a0enter below scopes<\/strong>\u00a0(or click Select Scopes). This will allow read\/write access to Drive Files or Sheet (E.g. Reading file content \/ Export file to CSV). You can use the Scope browser to see many other available permissions. For more information click here.<\/a>\n
      https:\/\/www.googleapis.com\/auth\/drive\r\nhttps:\/\/www.googleapis.com\/auth\/drive.file\r\nhttps:\/\/www.googleapis.com\/auth\/drive.readonly\r\nhttps:\/\/www.googleapis.com\/auth\/spreadsheets\r\nhttps:\/\/www.googleapis.com\/auth\/spreadsheets.readonly<\/pre>\n<\/li>\n
    7. Click on the Generate Token button.\n
    8. You will see UI as below\u2026 Click Allow (You may have to scroll to see that button sometimes).<\/li>\n
    9. Click on Test Connection to see the connection is working and click on the OK button to save\u00a0configure setting UI.<\/li>\n<\/ol>\n
      Find Google Sheet ID for API calls<\/span><\/h2>\n
      Before we can write or update Sheet data in SSIS we need to know Google Driver File ID. We will use this ID in the next few sections.<\/p>\n
      The easiest way to find File ID is to look at the URL like below. If you need to know ID at runtime dynamically then refer to the last section.<\/p>\n
      \"Get<\/a>
      Get Google Drive File ID for API call (Sheet ID)<\/p><\/div>\n
      Create Variables to make dynamic URL<\/h2>\n
        \n
      1. Here, we are going to make a Dynamic URL value.<\/li>\n
      2. In Visual Studio Project Right Click on Design Panel, Select Variables.\n
        \"Create<\/a>
        Create Variables<\/p><\/div><\/li>\n
      3. Let’s Create Variables with correct DataType and Value. In variable “varRange<\/strong>” set only the starting point(Sheet1!A2<\/strong>) of data if you don’t know the ending point and leave the first-row for giving header row later or click here.<\/a>
        \n        \"Create<\/a><\/li>\n<\/ol>\n
        Write SQL Server data to Google Spreadsheet<\/h2>\n
        Now let\u2019s look at a real-world example. How to write data from SQL Server table or other source and send to Google Sheet using Web API Destination.<\/p>\n
          \n
        1. Drag and Drop SSIS Data Flow Task from SSIS Toolbox.\n
        2. Double click on the Data Flow task to see the Data Flow designer surface.<\/li>\n
        3. Here, In Visual Studio, drag and drop the OLE DB Source, JSON Generator Transform and Web API Destination in the design surface and join the components with the blue arrow.\n
        4. Here, you can use our Free ZS Dummy Data Source too. Click\u00a0here<\/a>\u00a0for more information.<\/li>\n
        5. But, we are going through OLE DB Source so we need OLEDB Connection.<\/li>\n<\/ol>\n
          Create OLE DB Connection<\/h3>\n
            \n
          1. Lets, create the OLE DB Connection.<\/li>\n
          2. Let’s, Right-click on Connection Managers Panel to Create OLEDB Connection, so you can use Source and Context Menu will appear, Select New OLEDB Connection from the Context Menu.\n
            \"Create<\/a>
            Create OLE DB Connection<\/p><\/div><\/li>\n
          3. Now, click on New Button to create Connection.\n
            \"Create<\/a>
            Create New Connection<\/p><\/div><\/li>\n
          4. Let’s Configure Connection Manager, just Follow steps one by one as we have created.\n
            \"Configure<\/a>
            Configure OLE DB Connection<\/p><\/div><\/li>\n
          5. Click on Test Connection to see correct configure it.<\/li>\n
          6. Click on the OK button to save connection configure setting UI.<\/li>\n<\/ol>\n
            Configure JSON Generator Transform<\/h3>\n
              \n
            1. Double click on\u00a0JSON Generator Transform to configure it.<\/li>\n
            2. Set Output Mode to Single Dataset Array<\/strong>. Right-click on Mappings, select Add Static Element then enter Name: “range<\/strong>” and Select variable we have created for value.\n
            3. Again,\u00a0Right-click on Mappings, select Add Static Element then enter Name: “majorDimension<\/strong>” and Value: “ROWS<\/strong>“.\n
            4. Now, Right-click on Mappings, select Add Document Array, Check to Treat as\u00a02D array<\/strong>\u00a0option and enter Element Name as “values<\/strong>“.\n
            5. Then, right-click on Document Array, select Add elements<\/strong>\u00a0> Select Add\u00a0Multiple Columns<\/strong>\u00a0and check on columns you like to add.\n
            6. Click on the OK button to save JSON Generator Transform configure setting UI.<\/li>\n<\/ol>\n
              Configure Web API Destination<\/h3>\n
                \n
              1. Double click on\u00a0Web API Destination to configure it.<\/li>\n
              2. Select the OAuth connection manager created in an earlier section (Assuming you had all scopes with write permission).<\/li>\n
              3. In the Setting tab,\n
                  \n
                1. In URL, Generate Dynamic\u00a0URL like below. Change Sheet ID, Range designator(including Sheet name) and API URL Parameters as per your need.\n
                  https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/{{User::varGoogleSpreadsheetID}}\/values\/{{User::varRange}}:{{User::varSpreadsheetValues}}?valueInputOption={{User::varValueInputOption}}<\/pre>\n
                  Example URL without variables (some part masked with xxxxxxxx):<\/strong><\/p>\n
                  https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/1tuGO3_-2JlSmyiHwX6xxxxxxxxCHrORJc\/values\/Sheet1!A2:B4:append?valueInputOption=USER_ENTERED<\/pre>\n<\/li>\n
                2. Change Request Method to\u00a0POST.<\/strong><\/li>\n
                3. Select JSON Generator variable into Body (Request Data)<\/strong> or you can select\u00a0Input column<\/strong>\u00a0for\u00a0Body\u00a0<\/strong>(JSON Generator Output).<\/li>\n
                4. Set\u00a0application\/JSON\u00a0<\/strong><\/strong><\/strong>to\u00a0Body Content-type.\n
                5. Click on the OK button to save Web API Destination configure setting UI.<\/li>\n
                6. That’s all, You are ready to Run or Execute Task and verify data into Google spreadsheet. Now update header row values for click here.<\/a>\n
                  \"Run<\/a>
                  Run or Execute Package and verify data into Google Spreadsheet<\/p><\/div><\/li>\n<\/ol>\n
                  Append data to Google SpreadSheet using REST API Task<\/span><\/h2>\n
                  Now let’s look at how to write to Google SpreadSheet. To write multiple rows in a single request. Also, check this link to\u00a0learn more<\/a>.<\/p>\n
                  For more information, you can see the\u00a0example here<\/a>. For parameter, detail review this API documentation.<\/p>\n
                  You can use\u00a0SSIS REST API Task<\/a>\u00a0or\u00a0Web API Destination<\/a>\u00a0to achieve the Spreadsheet write scenario. For a simple example, let’s use the REST API Task.<\/p>\n
                    \n
                  1. Drag and Drop ZS REST API Task on the control flow designer.\n
                  2. Double click on ZS REST API Task to configure it.<\/li>\n
                  3. In the Request Settings tab,\n
                      \n
                    1. set Request URL Access Mode to Url from a connection.\u00a0Select OAuth Connection from the dropdown (Same connection we created in the previous section).<\/li>\n
                    2. Enter the URL below. Change Sheet ID, Range designator(including Sheet name) and API URL Parameters as per your need.\n
                      https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/{{User::varGoogleSpreadsheetID}}\/values\/{{User::varRange}}:{{User::varSpreadsheetValues}}?valueInputOption={{User::varValueInputOption}}<\/pre>\n
                      Example URL without variables (some part masked with xxxxxxxx):<\/strong><\/p>\n
                      https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/1tuGO3_-2JlSmyiHwX6xxxxxxxxCHrORJc\/values\/Sheet1!A1:append?valueInputOption=USER_ENTERED<\/pre>\n<\/li>\n
                    3. Change Request Method to\u00a0POST.<\/strong><\/li>\n
                    4. Enter Request Body as below
                      \n
                      Note:<\/strong> For example purpose, we included header line in data cells but in the real-world header may already exist in the sheet.<\/div><\/div>\n
                      {\r\n  \"range\": \"{{User::varRange}}\",\r\n  \"majorDimension\": \"ROWS\",\r\n  \"values\": [\r\n    [\"Item\", \"Cost\", \"Stocked\", \"Ship Date\"],\r\n    [\"Wheel\", \"$20.50\", \"4\", \"3\/1\/2016\"],\r\n    [\"Door\", \"$15\", \"2\", \"3\/15\/2016\"],\r\n    [\"Engine\", \"$100\", \"1\", \"3\/20\/2016\"],\r\n    [\"Totals\", \"=SUM(B2:B4)\", \"=SUM(C2:C4)\", \"=MAX(D2:D4)\"]\r\n  ]\r\n}<\/pre>\n<\/li>\n
                    5. Change Request Content type to\u00a0application\/JSON.<\/strong><\/strong><\/strong>\n
                    6. Click on the OK button to save REST API Task configure setting UI.<\/li>\n
                    7. That’s all, You are ready to Run or Execute Task and verify data into Google spreadsheet.\n
                      \"Execute<\/a>
                      Execute REST API Task and verify Google Spreadsheet data<\/p><\/div><\/li>\n<\/ol>\n
                      Update Google Sheet Cells<\/span><\/h2>\n
                      Now let\u2019s look at the below example of updating multiple cells in google Spreadsheet. For more information, you can see the\u00a0example here<\/a>. For parameter, detail review this API documentation.<\/p>\n
                      You can use\u00a0SSIS REST API Task<\/a>\u00a0or\u00a0Web API Destination<\/a>\u00a0to achieve the Spreadsheet Update scenario. For a simple example, let’s use the REST API Task.<\/p>\n
                      Update Google Sheet header cells manually<\/h3>\n
                        \n
                      1. Drag and drop ZS REST API Tasks on the control flow designer.<\/li>\n
                      2. Double click on the First REST API Task to configure it.<\/li>\n
                      3. In the Request Settings tab,\n
                          \n
                        1. Enter the URL below. Change Range designator(including Sheet name: Sheet1!A1<\/strong>) and API URL Parameters as per your need.\n
                          https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/{{User::varGoogleSpreadsheetID}}\/values\/{{User::varRange}}?valueInputOption={{User::varValueInputOption}}<\/pre>\n
                          Example URL without variables (some part masked with xxxxxxxx):<\/strong><\/p>\n
                          https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/1tuGO3_-2JlSmyiHwX6xxxxxxxxCHrORJc\/values\/Sheet1!A1?valueInputOption=USER_ENTERED<\/pre>\n<\/li>\n
                        2. Change Request Method to\u00a0PUT.<\/strong><\/li>\n
                        3. Enter Request Body as below\n
                          {\r\n  \"range\": \"{{User::varRange}}\",\r\n  \"majorDimension\": \"ROWS\",\r\n  \"values\": [\r\n    [\"CustomerID\", \"CustomerName\"]\r\n  ]\r\n}<\/pre>\n<\/li>\n<\/ol>\n<\/li>\n
                        4. Click on the OK button to save REST API Task configure setting UI.<\/li>\n
                        5. That’s all, You are ready to Run or Execute Task and verify data into Google spreadsheet.
                          \n\"\"<\/a><\/li>\n<\/ol>\n
                          Single Range (Set Values for multiple cells)<\/span><\/h3>\n
                            \n
                          1. Follow the previous section(write data to Google Spreadsheet) to update sheet data.<\/li>\n
                          2. In the Request Settings tab,\n
                              \n
                            1. Enter the URL below. Change Sheet ID, Range designator(including Sheet name) and API URL Parameters as per your need.\n
                              https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/{{User::varGoogleSpreadsheetID}}\/values\/{{User::varRange}}?valueInputOption={{User::varValueInputOption}}<\/pre>\n
                              Example URL without variables (some part masked with xxxxxxxx):<\/strong><\/p>\n
                              https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/1tuGO3_-2JlSmyiHwX6xxxxxxxxCHrORJc\/values\/Sheet1!A2:B4?valueInputOption=USER_ENTERED<\/pre>\n<\/li>\n
                            2. Change Request Method to\u00a0PUT.<\/strong><\/li>\n
                            3. Enter Request Body as below\n
                              {\r\n  \"range\": \"{{User::varRange}}\",\r\n  \"majorDimension\": \"ROWS\",\r\n  \"values\": [\r\n    [\"Laptop\", \"$2000.50\"],\r\n    [\"Mobile\", \"$1150\"]\r\n  ]\r\n}<\/pre>\n<\/li>\n<\/ol>\n<\/li>\n
                            4. That’s all, click on the Test Request\/Response button and verify data into Google spreadsheet.\n
                              \"Calling<\/a>
                              Calling Google Sheet API to Update Multiple Cell Values<\/p><\/div><\/li>\n<\/ol>\n
                              Clear data to Google SpreadSheet using REST API Task<\/span><\/h2>\n
                              Clear a sheet of all values while preserving formats<\/h3>\n
                              Lets clear a sheet of all values by REST API Task, Before processed we need sheet Id. So let’s get the SheetId by API Call. For more information about click here.<\/a><\/p>\n
                                \n
                              1. Drag and Drop two ZS REST API Tasks on the control flow designer and rename(Get Sheet ID and Clear Google Sheet of all values) it.<\/li>\n
                              2. Double click on the First REST API(Get Sheet ID) Task to configure it.<\/li>\n
                              3. In the\u00a0Request Settings tab,\n
                                  \n
                                1. Enter the URL below. Change Sheet ID and API URL Parameters as per your need.\n
                                  https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/{{User::varGoogleSpreadsheetID}}<\/pre>\n
                                  Example URL without variables (some part masked with xxxxxxxx):<\/strong><\/p>\n
                                  https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/1tuGO3_-2JlSmyiHwX6xxxxxxxxCHrORJc<\/pre>\n<\/li>\n
                                2. Change Request Method to\u00a0GET.
                                  \n<\/strong><\/strong>                                  \"REST<\/a>REST API Task – Get Google Sheet ID<\/li>\n<\/ol>\n<\/li>\n
                                3. Now go to Response Settings Tab, Select Response content Type Json and Write Response Content Filter Expression. Check on Save Response Content. Select Save Mode to Variable and Create a New variable or select it.
                                  \nExample of Response Content Filter Expression for the first Sheet :\u00a0<\/strong><\/p>\n
                                  $.sheets.[0].properties.sheetId<\/pre>\n
                                  \"REST<\/a>
                                  REST API Task – Save Google Sheet ID into Variable<\/p><\/div><\/li>\n
                                4. Now, click on the Test Request\/Response to check configures are ok.<\/li>\n
                                5. Click on the OK button to save REST API Task configure setting UI.<\/li>\n
                                6. Double click on the second REST API(Clear Google Sheet of all values) Task to configure it.<\/li>\n
                                7. In the\u00a0Request Settings tab,\n
                                    \n
                                  1. Enter the URL below. Change Sheet ID and API URL Parameters as per your need.\n
                                    https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/{{User::varGoogleSpreadsheetID}}:batchUpdate<\/pre>\n
                                    Example URL without variables (some part masked with xxxxxxxx):<\/strong><\/p>\n
                                    https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/1tuGO3_-2JlSmyiHwX6xxxxxxxxCHrORJc:batchUpdate<\/pre>\n<\/li>\n
                                  2. Change Request Method to\u00a0POST.\u00a0<\/strong>Enter Request Body as below and change a variable parameter\u00a0as per your need.\n
                                    {\r\n  \"requests\": [\r\n    {\r\n      \"updateCells\": {\r\n        \"range\": {\r\n          \"sheetId\": {{User::varGoogleSheetID}}\r\n        },\r\n        \"fields\": \"userEnteredValue\"\r\n      }\r\n    }\r\n  ]\r\n}<\/pre>\n<\/li>\n
                                  3. Change Request Content type to\u00a0application\/JSON.
                                    \n<\/strong><\/p>\n
                                  4. Click on the OK button to save REST API Task configure setting UI.<\/li>\n
                                  5. That’s all, You are ready to Run or Execute Task and verify data into Google spreadsheet.<\/li>\n<\/ol>\n
                                    Clears range cell values from a spreadsheet<\/h3>\n
                                      \n
                                    1. Double click on REST API Task to configure it.\u00a0Then, follow the way just like the previous section configures REST API Task.<\/li>\n
                                    2. Here you need to set the value of Variable “varSpreadsheetValues<\/strong>” to “clear<\/strong>” or you can change it directly in URL append to clear <\/strong>and value of variable “varRange<\/strong>” (Change cell range and sheet name) as per your need. For more information about it click here.<\/a>\n
                                        \n
                                      1. Enter the URL below. Change Sheet ID, Range designator(including Sheet name) and API URL Parameters as per your need.\n
                                        https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/{{User::varGoogleSpreadsheetID}}\/values\/{{User::varRange}}:{{User::varSpreadsheetValues}}?valueInputOption={{User::varValueInputOption}}<\/pre>\n
                                        Example URL without variables (some part masked with xxxxxxxx):<\/strong><\/p>\n
                                        https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/1tuGO3_-2JlSmyiHwX6xxxxxxxxCHrORJc\/values\/Sheet1!A2:B4:clear?valueInputOption=USER_ENTERED<\/pre>\n<\/li>\n<\/ol>\n<\/li>\n
                                      2. Change Request Method to\u00a0POST.<\/strong><\/li>\n
                                      3. Enter Request Body as below\n
                                        {}<\/pre>\n<\/li>\n
                                      4. Change Request Content type to\u00a0application\/JSON.<\/strong><\/strong><\/strong><\/li>\n
                                      5. That’s all, click on the Test Request\/Response button and verify data into Google spreadsheet.<\/li>\n<\/ol>\n
                                        Conclusion<\/span><\/h2>\n
                                        Google Drive API and Google Sheets API provide a great way to automate file-related functionality. However, to call Google API \u00a0you have to use the SDK \/ coding approach (e.g. C#, Java, Python, Ruby). Luckily ZappySys\u00a0SSIS PowerPack<\/a>\u00a0provides a great way to integrate any Google API call via simple drag and drop approach without coding. Try out yourself see how long it takes to call virtually any REST API.<\/p>\n
                                        References<\/h2>\n
                                        Finally, you can use the following links for more information:<\/p>\n