Google Sheets Connector - Source Code
<?xml version="1.0" encoding="utf-8"?>
<ApiConfig Name="Google Sheets"
Desc="Read / write Google Sheets data inside your app, perform many Google Sheets operations without coding using easy to use high performance API Connector for Google Sheets"
Slug="google-sheets-connector"
Id="7895d782-e54c-4624-aa96-437cd3e92fa8"
Logo="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAJYAAACWCAYAAAA8AXHiAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAA6LSURBVHhe7d3pk1XFGcdx/4JIcAFmY0A2RfRFNAm+CC9TVipalXIEZuZeZkbcKNxQzEIlauKG7KDAyCzMwjYsOhNjUikBLcKUBkRcYqJWGRC01BgRCPqy08+d28O55/76nO5z+gK3u5+q7xsLOMM9H/uc0/fOcJEfP378+PHjx48fP378+PFzfuY3b3SzG196lN2w6+HUTd+10Eg/3il6SKkfxfZgQT996RGW/+v7MTk/3LWw7gc7Hhgc33sHq+2da7DbCqpJUs9Q1T0tRqrqbi7q2r572aq3/8jWvDMwPf+S+Ek712y7p2/KlnlsXO/teQymKg9UFMFafrifrTg8cMzjMjBTt83vI1Auo6IErGWHX2Sr+cqVf3n8JJkZ/b+qm7p1vvOoqCAsgcuvXAnnuh0LBs2iKgRVLqioMCxqxeF+f1lMMhM23clPPgKStPJERV27rRgWtfLtAX9Z1B2MI2nli4qSwaLosrjiTb9yKc2v97X3YSBJKm9UVBQsyl8WFeeXr7XnUaSt/FFRcbAo/7SoMAtfbcvDSJMdqCgVWNQqfs/lV66IeWjvhjyOJBWCKndUlCosii6L/p5LMiZhQTRxXUCoKB1YlL8sSiY5LPtQUddowqL8e4tgksGyE1UlLwksyj8thkYflr2oqGnb7kkEi/KXxcDowbIbVUV3UypYFD0t+ht6Puqw7EdlAha14i1+WXQdlxosN1CZgkXRDX3+JXZz4mG5g8okLMrpj9xEw3ILlWlYlLNPi3JY7qGiTMOi6IY+/3K7M8WwCkG5hIoqBSyKPs/l1NNiHCyIJq4yRUWVChbl1NNiISy3UVGlhEU5s4l6FpZHRV25dR5bCkCYzImP3Dy093mPKhB9U8nTh3ZAECaz/mkxDAuiicsSVKL5+1ohBtNZvYkahAXRxGUZKuravvvOyapF0dOilSuXgAXRxGUhKlF294qS32uJrLwsEiyIJi6LUVGTt9zNFuxvhxBKkXXft5gIluWoqDHdc9hV/Alx3t617IkDfRCD6ax6b1EbliOoRLVdLewnvQtYy5+Wscde38wWH9qZQ0B7XaVopS0/5UYLluWogqDGdPE25mvLsDFL61jF07ew8ctms4krM2xSTBNXNiauedfi8r8sKsNyCVUYFtXayCqX1LGqxbfEVpE0DpeaveX3jsByHVUAV9XSW1nVM7I4PM0qgy0eauamRx2AZTkqSglVvsrWDKteMpOjMAMLNbPXgp+DGgnLo4JVrm1gNRxXsKolHFbSQkDren5nMSyXUBEoRVS5Ouewquc4rqWz8nFcKaIV8GwcVq+tsCxHVbBKCVgIUExDuDCWNN1q5aXQo1KvI8Oqnq1nY5fPiq0m3PBqF8xWWJajokyhGr0xm4tw1ayuZ7XLZ0c2Ntyys9Us46gCzdxs01OhR6WcQBVMBZdqs2yA9eAeDsujUg6hoiraM2zs6gZWu4ID0wnCesweWAhIkhAS3UyiKgJVAlSiirYMG7eqgY1bGVd9ZLO2WAILAUkSQqJbyVAJWACMSggSqqIty8avamTjV+IwtMKseEtnwZ5WiEQ3hES3ckclqtzAca3hkFYXw1LBNXurh5ULIdHNJCrqfKEaRXVmWXVrll2xOsMmFNTIruCrWbjcCheofusfPCyERDebUF0eqHp9lmMShZGdjQAGq9/mOCyERLeSoSJQ5xGVaOw6jmoNB6RRg8uwEBLdTKIqWKUogEU1BCcqGSpqdGeG1a7LsInPZpVr6HvcTVgIiW4lRXUBrFRDZXKN4rgmrsWIUI3bHYSFkOhmEhV1IaMS0co1Yd0cNum5+JyDhZDo5iIqahSPducJ1+S1TZFltj/hDiyERDdXUQWrbM9CTMGcgYWQ6FYyVClAUQhOVGlQiQjXpPVNbMo6XGaHA7AQEt1MoipYpSiARTUEJyoTqETV7fyS2NrMrlzfMtyU9c25sjuetBsWQqJbSVGVyeVPVm17UwEsUXbnU/bCQkh0M4mKsgkVRTf04zfwVauVgwo0Z5elsBAS3TwqtWiPa1wbX7me56jyWQkLIdHNo1Lvso2N7HLa4+Ir19QNc3M17XraLlgIiW4mURWAogAW1RCcqEqNikAFG8NxTWprYVe3cVgvWAQLIdGtZKholUqxUhES3UoFigqjEtHu/OT2Ftb8ogU/FIRgISS6CVSjurJs4MNB9sEXn1wgHS1pL330Oru0owECkoVQiSo6syzbb8mKhaDoFFypRnVl2P6j77FTp0450RvH3mcjO+ohIBTCFOzSzkb2iwFL3itEWFQLovKwokOQghEq6uYBSz6PhcCoFEblYeEQonAClfOwECoPKz0qp2HJUEXB+uLEV2z3x4eM9grvs6+/hMd65eM34e9J2qv/Psz+d/p00bGiYCFE4cKonIUVhYqSwTr46Ye5Fzv4awu2FMS2AtgukEUn4dWj7xQd68CnH+RONtqfouTbCVQxEGpc71z23bffFR0rDawwKJFzsOJQUYlhJdinopNw7mDdrgULQQoWxhTMKVgqqChVWGlRUXQS9h55u+hYUbCSoKJ0YCFIwcKQwjkDSxUVFQfLBCjRpZ0NbM+Rw0XHksFKs6OuAgshCocghXMClg4q6lzBIig6sJKuVKI4WAhROIQIZT0sXVSUDBadbHpxTaHSgZUWFZUWFgIk6+Z+S352gylUlBIsA6hUYZlARaWBhfBI62iwF1ZSVFQsLEOoVGCZQkUlhQXxyOKorIWVBhUVBWtkx2x2GXpBleMvfKAR7bMgrL8f/xe7uH0mu4T/mlz8ZIlGttcnqrq7RRsW/jtIyqOyElZaVJQM1n+++Zod5Cf8TX4Tr9vB4x/kfi/q8xNfSY9lsrf41xE+DiWDBfHICqCyDpYJVJQMlq0hWBCPrBAqq2CZQkW5DgvikQVQUVbBQkiSdHlXo7OwIB5ZAJTIGlgISJJoK8FVWBCPLIBJdAn/s26yAdYDe9ZDJDoNb3oSLP5/LoL15Yn/sn2fvMv+duwf5vrkPXjzLo5lskF+vKiPzUBAKICJGnpq5U+gHtZQQVQ5WPzFQ7DE3hLaIxKJ+xTVRnTMhtsNbxz/J/te+yx4AulrSBI94ERtN0BE4cDXIxKoKOdhhVHR5mcULHpx0cYnFd78jIs2P2mPCm6QHo9HrFIQcW3vbelgAUyiICoPixdGRUCSwEJwohI76qWEFUSVGhbAJAqjopyGhVBRurAQnKiCb9OUClYYVSpYAJMIoaKchVUAKvTenw4sBCeq8Ht/pYCFUFGJYAFMVPBGHeUcrIJVigogEanCQnCiQm8om4SFMAXThhXCFCyICOUUrCJUoZVKpAILwYkKoTIJC0EKV9ujAQtgEkWtVCJ3YUlQUXGwEJyoZKiokZ315wQWfd1jVWEBTCIVVNRN/Rb8s3IqsFRRUTJYhz77KHc5uaL3dq3GR1TT08JeO/pu0bEOHv8w9zEX+gxVdHNz0dcljYMiVFO3zGPfnomBBTCJVFFRTsDSQUXJYNGuNZ2YpNFqgUK74fTf0K9NE30N4eNQw7AAJpEOKsp6WDqgRDJYtkawRnTgXX5KFxVlLayCVYoCgGR5WOlQUVbCKkKlsVpRHlY6VJT9sDRRUVGwTp86bbRvTp5kJ0+dLDrOSd7X35wwXvg4FIKVBhVlHay0qCj6Zon94EntyFefsWcObGdLDu6I7oB68/c+l3vaDB/r/c+PsLqXn2S3/mVxrro/P5W67F+XsTNnzhQdKwwrLSrKKlgmUFH02I1g0T4WvWg6+1RozymY6j4W2psKNrwHFVHUPpaAZQIVZQ2sAlQAi2oEhb4tSxVWGlRUHCyEKBxChIqDFYSRNvtgpVipBBZVWGlRUVGwvk/fwwggBUOAZI3tkX9foYcFZhiWAVSqsEygoqJg0clGmEQIjzR+mfOwNCcHyxAqFVimUFFJYUE8svI35R6W5ty/m8MCYFQKo1KBhUFRGE9USWBBPLLyqDysBJMUFkJFJYOF4cSlCwvikRVA5WElmCSwECiRPiyMRiUdWBCPrBAqDyvB6MJCmILJYNFP9KvoauInaOhjKFR1rrP/pJ1uFfzecO/R4p9BSh+bGd2VZVU9zUN16dQEm7z5LvgJBw9LMjqwEKRwMlgUvf1iOnQcCv3atKHjeFiSUYWFEKGiYNmYhyUZFVgIkCwPK31OwEJ4ovKw0kV/1s/7H7UbFoITFW1+0nfOeFjJoj/HelgITlRiR/2yjRm2aH8Xe/atgXPeGq36jbRosMsILHpv03pYCE5UJt+mkRXck0LB/ShZYJ9KZOqjLzoFUVkLC8GJyqNKVxiVlbAQnKjkqDAQ3RCicBCPLICJivtZCqUKobIOFoITValXKoQoHMQjK4Qp2PlAhUCJrIGF4ETlL3/pQpiCWQHrvt3rIB5ZHlW6ZJe/YM7B8qjSpYKKcgqWR5UuVVSUM7DkqDAQ3RCicBCPLICJOl9PfwhPVE7AKvVKhRCFg3hkhTAFKwdUlPWw/OUvXQhNbO0c1osWw/Ko0qVzTzUcRzWizWJYHlW60qCyFpYtqC60t2kiC6CyEpZNN+ropJc6iCauECprYNF3QtuGqpxu1MOocrBs+Gjy4/t6+vw9VbpMXP6CXdw2sy5/esp7Rm+cw0++R5Uk06hGttG/9J+xA9a0LfM5AI9Kt0SoKABKVNvVwmZsX2gHrAndd9RVdjVzCB6VahBMXBErFXUxr2ZjU1/+tJT/zNj5cNW47rmDplAhROEgHlkAk8gmVJWdc9gNm+6bnj8t9kxt91z+dIixqIYQhYN4ZAFMIptQjenIlP+TYNRM33z/4JSeO9moTgwnLgQpGMQjC2AS2XCjTlV0ZNn1vfceu97GlSo4j7z8fNXPti6qG82fTG7ctohdt+leNq13nkZ3S7u6R6Puu6RN5V3Vfec578okdRU3lTdj04Psup57lk/rvrvutwPr7Ublx48fP378+PHjx48fP34uqLnoov8DjXDR6VwJy3EAAAAASUVORK5CYII="
Version="8"
EngineVersion="17"
>
<VersionHistory>
<Change Ver="8" Type="New" Date="2025-01-24">Added images in User Account and Service Account authentication instructions</Change>
<Change Ver="8" Type="New" Date="2025-01-24">Added ImpersonateAs property for Service Account authentication (Requires Domain-wide delegation ON and needs Google Workspace account)</Change>
<Change Ver="7" Date="2021-10-26" Type="New">Increased BatchSize to 10000000 for update_sheet_data</Change>
<Change Ver="7" Date="2021-10-26" Type="New">Added MaxRequestSize in insert_sheet_data to auto adjust Request to avoid going over 10MB data limit for insert operation. When large request sent you may get Internal error 500</Change>
<Change Date="2024-06-05" Type="New">Added Redirect URL property to support Web Application credentials (visible when show advanced option checked)</Change>
<Change Date="2023-09-07" Type="Modified">Made SpreadSheetId required on connection UI</Change>
<Change Date="2022-10-13" Type="Modified">Added retry logic on error code 429 (Too many requests).</Change>
<Change Date="2020-05-21" Type="New">Added examples and support for Tables (Sheet Tab name can be used as Table)</Change>
<Change Date="2020-05-15" Type="New">Added support for Service Account Credentials (Using P12 key file)</Change>
<Change Date="2020-05-15" Type="Fix">UseCustomApp option doesnt work</Change>
<Change Date="2020-12-01">Initial version</Change>
</VersionHistory>
<ServiceUrls>
<ServiceUrl Name="https://sheets.googleapis.com/v4/spreadsheets" Url="https://sheets.googleapis.com/v4/spreadsheets" />
</ServiceUrls>
<Auths>
<Auth Name="OAuth" Label="User Account" HelpLink="https://zappysys.com/links/?id=10002"
Type="OAuth" ConnStr="Provider=GoogleSheets;ScopeSeparator={space};"
Desc="User accounts represent a developer, administrator, or any other person who interacts with Google APIs and services. User accounts are managed as Google Accounts, either with Google Workspace or Cloud Identity. They can also be user accounts that are managed by a third-party identity provider and federated with Workforce Identity Federation."
TestEndPoint="get_sheet_properties"
TestParameters="SpreadSheetId=1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
>
<Notes>
<![CDATA[
<p>
Follow these steps on how to create Client Credentials (User Account principle) to authenticate and access Google Sheets API in SSIS package or ODBC data source:
</p>
<div class="alert alert-warning">
<strong>WARNING</strong>:
If you are planning to automate processes, we recommend that you use a <em>Service Account</em> authentication method.
In case, you still need to use <em>User Account</em>, then make sure you use a system/generic account (e.g. <code>automation@my-company.com</code>).
When you use a personal account which is tied to a specific employee profile and that employee leaves the company,
the token may become invalid and any automated processes using that token will start to fail.
</div>
<h3>Step-1: Create project</h3>
<p>
This step is optional, if you already have a project in Google Cloud and can use it.
However, if you don't, proceed with these simple steps to create one:
</p>
<ol>
<li>
<p>
First of all, go to <a target="_blank" href="https://console.developers.google.com/">Google API Console</a>.
</p>
</li>
<li>
<p>
Then click <strong>Select a project</strong> button and then click <strong>NEW PROJECT</strong> button:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/start-creating-new-project-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Start creating a new project in Google Cloud"
title="Starting creating a new project in Google Cloud"
width="1000"
height="340" />
</li>
<li>
<p>
Name your project and click <strong>CREATE</strong> button:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/create-new-project-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Create a new project in Google Cloud"
title="Creating a new project in Google Cloud"
width="680"
height="360" />
</li>
<li>
<p>
Wait until the project is created:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/wait-until-project-is-created-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Wait until project is created in Google Cloud"
title="Waiting until project is created in Google Cloud"
width="640"
height="390" />
</li>
<li>
Done! Let's proceed to the next step.
</li>
</ol>
<h3>Step-2: Enable Google Cloud APIs</h3>
<p>
In this step we will enable Google Sheets API and Google Drive API:
</p>
<ol>
<li>
<p>
Select your project on the top bar:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/select-project-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Select project in Google Cloud"
title="Selecting project in Google Cloud"
width="1000"
height="400" />
</li>
<li>
<p>
Then click the <strong>"hamburger"</strong> icon on the top left and access
<a target="_blank" href="https://console.cloud.google.com/apis/dashboard">APIs & Services</a>:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/access-apis-and-services-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Access APIs and services in Google Cloud"
title="Accessing APIs and services in Google Cloud"
width="990"
height="440" />
</li>
<li>
<p>
Now let's enable several APIs by clicking <strong>ENABLE APIS AND SERVICES</strong> button:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/enable-api-for-project-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Enable API for project in Google Cloud"
title="Enabling API for project in Google Cloud"
width="800"
height="300" />
</li>
<li>
<p>
In the search bar search for <code>sheets</code> and then locate and select <strong>Google Sheets API</strong>:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/search-for-api-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Search for API in Google Cloud"
title="Searching for API in Google Cloud"
width="1000"
height="390" />
</li>
<li>
<p>
Then enable <strong>Google Sheets API</strong>:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/enable-google-sheets-api.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Enable Google Sheets API"
title="Enabling Google Sheets API"
width="620"
height="350" />
</li>
<li>
<p>
Again, repeat the step and enable <strong>Google Drive API</strong> as well:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/enable-google-drive-api.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Enable Google Drive API"
title="Enabling Google Drive API"
width="620"
height="350" />
</li>
<li>
Done! Let's proceed to the next step.
</li>
</ol>
<h3>Step-3: Create OAuth application</h3>
<ol>
<li>
<p>
First of all, click the <strong>"hamburger"</strong> icon on the top left and then hit <strong>VIEW ALL PRODUCTS</strong>:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/user-account/view-all-products-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="View all products in Google Cloud"
title="Viewing all products in Google Cloud"
width="990"
height="700" />
</li>
<li>
<p>
Then access
<a target="_blank" href="https://console.cloud.google.com/auth">Google Auth Platform</a>
to start creating an OAuth application:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/user-account/open-google-auth-platform-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Open Google Auth Platform in Google Cloud"
title="Opening Google Auth Platform in Google Cloud"
width="1000"
height="430" />
</li>
<li>
<p>
Start by pressing <strong>GET STARTED</strong> button:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/user-account/start-creating-app-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Start creating an app in Google Cloud"
title="Starting creating an app in Google Cloud"
width="1000"
height="340" />
</li>
<li>
<p>
Next, continue by filling in <strong>App name</strong> and <strong>User support email</strong> fields:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/user-account/fill-app-info-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Fill app info in Google Cloud"
title="Filling app info in Google Cloud"
width="850"
height="440" />
</li>
<li>
<p>
Choose <strong>Internal</strong> option, if it's enabled, otherwise select <strong>External</strong>:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/user-account/choose-app-audience-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Choose app audience in Google Cloud"
title="Choosing app audience in Google Cloud"
width="850"
height="590" />
</li>
<li>
<p>
<em>Optional step if you used <code>Internal</code> option in the previous step</em>.
Nevertheless, if you had to use <code>External</code> option, then click <strong>ADD USERS</strong> to add a user:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/user-account/add-test-user-in-google-cloud-app.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Add test user in Google Cloud app"
title="Adding test user in Google Cloud app"
width="1000"
height="990" />
</li>
<li>
<p>
Then add your contact <strong>Email address</strong>:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/user-account/enter-app-contact-info-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Enter app contact info in Google Cloud"
title="Entering app contact info in Google Cloud"
width="850"
height="510" />
</li>
<li>
<p>
Finally, check the checkbox and click <strong>CREATE</strong> button:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/user-account/create-app-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Create app in Google Cloud"
title="Creating app in Google Cloud"
width="850"
height="580" />
</li>
<li>
Done! Let's create Client Credentials in the next step.
</li>
</ol>
<h3>Step-4: Create Client Credentials</h3>
<ol>
<li>
<p>
In
<a target="_blank" href="https://console.cloud.google.com/auth">Google Auth Platform</a>,
select <strong>Clients</strong> menu item and click <strong>CREATE CLIENT</strong> button:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/user-account/start-creating-app-client-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Start creating app client in Google Cloud"
title="Starting creating app client in Google Cloud"
width="1000"
height="340" />
</li>
<li>
<p>
Choose <code>Desktop app</code> as <strong>Application type</strong> and name your credentials:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/user-account/create-oauth-app-client-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Create OAuth app client in Google Cloud"
title="Creating OAuth app client in Google Cloud"
width="1000"
height="500" />
</li>
<li>
<p>
Continue by opening the created credentials:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/user-account/view-app-client-credentials-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="View app client credentials in Google Cloud"
title="Viewing app client credentials in Google Cloud"
width="1000"
height="370" />
</li>
<li>
<p>
Finally, copy <strong>Client ID</strong> and <strong>Client secret</strong> for the later step:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/user-account/use-client-id-and-secret-to-read-google-rest-api-data.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Use client ID and secret to read Google REST API data"
title="Using client ID and secret to read Google REST API data"
width="770"
height="360" />
</li>
<li>
Done! We have all the data needed for authentication, let's proceed to the last step!
</li>
</ol>
<h3>Step-5: Configure connection</h3>
<ol>
<li>
<p>
Now go to SSIS package or ODBC data source and use previously copied values in <strong>User Account</strong> authentication configuration:
</p>
<ul>
<li>In the <strong>ClientId</strong> field paste the <strong>Client ID</strong> value.</li>
<li>In the <strong>ClientSecret</strong> field paste the <strong>Client secret</strong> value.</li>
</ul>
</li>
<li>
<p>
Press <strong>Generate Token</strong> button to generate Access and Refresh Tokens.
</p>
</li>
<li>
<p>
Open your Google Sheets spreadsheet in the browser, e.g.:
<code>https://docs.google.com/spreadsheets/d/1QM6WmGloHeGCliCqV3nEj8P-R1RbS59t5xJutN99rU/edit</code>.
</p>
</li>
<li>
<p>
Extract the spreadsheet Id from the URL and copy it to the clipboard, e.g. <code>1QM6WmGloHeGCliCqV3nEj8P-R1RbS59t5xJutN99rU</code>.
</p>
</li>
<li>
<p>
Go back to authentication configuration window and paste it into <strong>Default SpreadsheetId</strong> field.
</p>
</li>
<li>
<p>
Finally, click <strong>Test Connection</strong> to confirm the connection is working.
</p>
</li>
<li>
<p>
Done! Now you are ready to use Google Sheets Connector!
</p>
</li>
</ol>
]]>
</Notes>
<Params>
<Param Name="UseCustomApp" Value="True" Required="True" Hidden="False" Desc="Use your own app credentials or inbuilt app provided by ZappySys for ease of use. If you choose UseCustomApp=true then make sure to obtain your own ClientId and Secret using steps provided (Click [Steps to Configure] link found next to Authentication Type dropdown)" />
<Param Name="ClientId" VisibleIf="UseCustomApp" RequiredIf="UseCustomApp" />
<Param Name="ClientSecret" Secret="True" VisibleIf="UseCustomApp" RequiredIf="UseCustomApp" />
<Param Name="Scope" Value="https://www.googleapis.com/auth/drive~https://www.googleapis.com/auth/spreadsheets" Required="True" MultiSelectSeparator=" " MultiSelect="True"
Options="spreadsheets (Read, edit, create, and delete all your Google Sheets spreadsheets)=https://www.googleapis.com/auth/spreadsheets;
spreadsheets.readonly (Read all your Google Sheets spreadsheets)=https://www.googleapis.com/auth/spreadsheets.readonly;
drive (Read, edit, create, download and delete all of your Google Drive files)=https://www.googleapis.com/auth/drive;
drive.readonly (Read and download all your Google Drive files)=https://www.googleapis.com/auth/drive.readonly"/>
<Param Name="SpreadSheetId" Required="True" Label="Default SpreadSheetId" Desc="Enter default spreadsheet Id which should be used if user do not supply when calling various operations supported by this connector. To obtain sheet Id. Open spreadsheet in browser and check URL. You will see example value like 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms that is your sheet id." Options="Enter Your Sheet Id=;Example Sheet Id=1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"/>
<Param Name="TabName" Label="Default Tab Name (i.e. Sheet1)" Desc="Enter default tab name for spreadsheet Id you supplied. If you keep it blank then it will use first tab by default." />
<Param Name="RetryMode" Value="RetryWhenStatusCodeMatch" Hidden="True" />
<Param Name="RetryStatusCodeList" Value="403|429" Hidden="True" />
<Param Name="RetryCountMax" Value="5" Hidden="False" />
<Param Name="ReturnUrl" Label="Redirect URL (Only for Web App)" Options=";https://zappysys.com/oauth/" VisibleIf="UseCustomApp" Desc="Only specifiy this if you have created Credential as Web Application rather than Desktop. In Desktop App you dont have to supply Return URL (its always localhost). When you keep this blank it uses default value http://localhost:[some_random_port_each_time] for redirect_url)" />
</Params>
</Auth>
<Auth Name="ServiceAccount"
Label="Service Account"
Desc="Service accounts are accounts that do not represent a human user. They provide a way to manage authentication and authorization when a human is not directly involved, such as when an application needs to access Google Cloud resources. Service accounts are managed by IAM."
HelpLink="https://cloud.google.com/docs/authentication#service-accounts"
Type="OAuth"
ConnStr="Provider=GoogleSheets;UseCustomApp=True;UseClientCertificate=True;CertificateStorageType=PfxFile;CertificatePath=[$PrivateKeyPath$];GrantType=ClientCredentials;"
TestEndPoint="get_sheet_properties"
TestParameters="SpreadSheetId=1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms">
<Params>
<Param Name="ClientId" Label="Service Account Email" Desc="This is service account email ID (e.g. some_name@my_project.iam.gserviceaccount.com)" Required="True" />
<Param Name="PrivateKeyPath" Label="Service Account Private Key Path (i.e. *.p12)" Desc="File path for p12 file (i.e. Private Key file for service account). Keep this key file secure" Required="True" />
<Param Name="Scope" Value="https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/spreadsheets" />
<Param Name="SpreadSheetId" Required="True" Label="Default SpreadSheetId" Desc="Enter default spreadsheet Id which should be used if user do not supply when calling various operations supported by this connector. To obtain sheet Id. Open spreadsheet in browser and check URL. You will example value like 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms that is your sheet id." Options="Enter Your Sheet Id=;Example Sheet Id=1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"/>
<Param Name="TabName" Label="Default Tab Name (i.e. Sheet1)" Desc="Enter default tab name for spreadsheet Id you supplied. If you keep it blank then it will use first tab by default." />
<Param Name="RetryMode" Value="RetryWhenStatusCodeMatch" Hidden="True" />
<Param Name="RetryStatusCodeList" Value="403|429" Hidden="True" />
<Param Name="RetryCountMax" Value="5" Hidden="True" />
<Param Name="ImpersonateAs" Type="Property" Key="ExtraAttributesForTokenRequest" Hidden="False" ValueTemplate="user={$value$}" Label="Impersonate As (Enter Email Id)" />
</Params>
<Notes>
<![CDATA[
<p>
Follow these steps on how to create Service Account to authenticate and access Google Sheets API in SSIS package or ODBC data source:
</p>
<h3>Step-1: Create project</h3>
<p>
This step is optional, if you already have a project in Google Cloud and can use it.
However, if you don't, proceed with these simple steps to create one:
</p>
<ol>
<li>
<p>
First of all, go to <a target="_blank" href="https://console.developers.google.com/">Google API Console</a>.
</p>
</li>
<li>
<p>
Then click <strong>Select a project</strong> button and then click <strong>NEW PROJECT</strong> button:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/start-creating-new-project-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Start creating a new project in Google Cloud"
title="Starting creating a new project in Google Cloud"
width="1000"
height="340" />
</li>
<li>
<p>
Name your project and click <strong>CREATE</strong> button:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/create-new-project-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Create a new project in Google Cloud"
title="Creating a new project in Google Cloud"
width="680"
height="360" />
</li>
<li>
<p>
Wait until the project is created:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/wait-until-project-is-created-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Wait until project is created in Google Cloud"
title="Waiting until project is created in Google Cloud"
width="640"
height="390" />
</li>
<li>
Done! Let's proceed to the next step.
</li>
</ol>
<h3>Step-2: Enable Google Cloud APIs</h3>
<p>
In this step we will enable Google Sheets API and Google Drive API:
</p>
<ol>
<li>
<p>
Select your project on the top bar:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/select-project-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Select project in Google Cloud"
title="Selecting project in Google Cloud"
width="1000"
height="400" />
</li>
<li>
<p>
Then click the <strong>"hamburger"</strong> icon on the top left and access
<a target="_blank" href="https://console.cloud.google.com/apis/dashboard">APIs & Services</a>:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/access-apis-and-services-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Access APIs and services in Google Cloud"
title="Accessing APIs and services in Google Cloud"
width="990"
height="440" />
</li>
<li>
<p>
Now let's enable several APIs by clicking <strong>ENABLE APIS AND SERVICES</strong> button:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/enable-api-for-project-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Enable API for project in Google Cloud"
title="Enabling API for project in Google Cloud"
width="800"
height="300" />
</li>
<li>
<p>
In the search bar search for <code>sheets</code> and then locate and select <strong>Google Sheets API</strong>:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/search-for-api-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Search for API in Google Cloud"
title="Searching for API in Google Cloud"
width="1000"
height="390" />
</li>
<li>
<p>
Then enable <strong>Google Sheets API</strong>:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/enable-google-sheets-api.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Enable Google Sheets API"
title="Enabling Google Sheets API"
width="620"
height="350" />
</li>
<li>
<p>
Again, repeat the step and enable <strong>Google Drive API</strong> as well:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/enable-google-drive-api.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Enable Google Drive API"
title="Enabling Google Drive API"
width="620"
height="350" />
</li>
<li>
Done! Let's proceed to the next step.
</li>
</ol>
<h3>Step-3: Create Service Account</h3>
<p>
Use the steps below to create a Service Account in Google Cloud:
</p>
<ol>
<li>
<p>
First of all, go to
<a target="_blank" href="https://console.cloud.google.com/iam-admin/iam">IAM & Admin</a>
in Google Cloud console:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/Service-Account/access-iam-and-admin-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Access IAM & Admin in Google Cloud"
title="Accessing IAM & Admin in Google Cloud"
width="690"
height="510" />
</li>
<li>
<p>
Once you do that, click <strong>Service Accounts</strong> on the left side
and click <strong>CREATE SERVICE ACCOUNT</strong> button:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/Service-Account/start-creating-service-account-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Start creating service account in Google Cloud"
title="Starting creating service account in Google Cloud"
width="1000"
height="500" />
</li>
<li>
<p>
Then name your service account and click <strong>CREATE AND CONTINUE</strong> button:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/Service-Account/create-service-account-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Create service account in Google Cloud"
title="Creating service account in Google Cloud"
width="750"
height="450" />
</li>
<li>
<p>
Continue by clicking <strong>Select a role</strong> dropdown
and start granting service account Project Viewer roles:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/Service-Account/start-granting-service-account-project-roles-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Start granting service account project roles in Google Cloud"
title="Starting granting service account project roles in Google Cloud"
width="750"
height="250" />
</li>
<li>
<p>
Find <strong>Project</strong> group
and select <strong>Viewer</strong> role:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/Service-Account/grant-service-account-project-viewer-role.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Grant service account project viewer role"
title="Granting service account project viewer role"
width="540"
height="410" />
</li>
<li>
<p>
Finish adding roles by clicking <strong>CONTINUE</strong> button:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/Service-Account/finish-granting-service-account-project-roles-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Finish granting service account project roles in Google Cloud"
title="Finishing granting service account project roles in Google Cloud"
width="750"
height="370" />
<div class="alert alert-info alert-title">
You can always add or modify permissions later in
<a target="_blank" href="https://console.cloud.google.com/iam-admin/iam">IAM & Admin</a>.
</div>
</li>
<li>
<p>
Finally, in the last step, just click button <strong>DONE</strong>:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/Service-Account/finish-configuring-service-account-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Finish configuring service account in Google Cloud"
title="Finishing configuring service account in Google Cloud"
width="750"
height="350" />
</li>
<li>
<p>
Done! We are ready to add a Key to this service account in the next step.
</p>
</li>
</ol>
<h3>Step-4: Add Key to Service Account</h3>
<p>
We are ready to add a Key (P12 certificate) to the created Service Account:
</p>
<ol>
<li>
<p>
In <a target="_blank" href="https://console.cloud.google.com/iam-admin/serviceaccounts">Service Accounts</a>
open newly created service account:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/Service-Account/open-service-account-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Open service account in Google Cloud"
title="Opening service account in Google Cloud"
width="750"
height="350" />
</li>
<li>
<p>
Next, copy email address of your service account for the later step:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/Service-Account/copy-service-account-email-address-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Copy service account email address in Google Cloud"
title="Copying service account email address in Google Cloud"
width="600"
height="370" />
</li>
<li>
<p>
Continue by selecting <strong>KEYS</strong> tab,
then press <strong>ADD KEY</strong> dropdown,
and click <strong>Create new key menu</strong> item:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/Service-Account/start-creating-key-for-service-account-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Start creating key for service account in Google Cloud"
title="Starting creating key for service account in Google Cloud"
width="750"
height="530" />
</li>
<li>
<p>
Finally, select <strong>P12</strong> option and hit <strong>CREATE</strong> button:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/google/Service-Account/create-p12-key-for-service-account-in-google-cloud.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Create P12 key for service account in Google Cloud"
title="Creating P12 key for service account in Google Cloud"
width="600"
height="400" />
</li>
<li>
P12 certificate downloads into your machine. We have all the data needed for authentication, let's proceed to the last step!
</li>
</ol>
<h3>Step-5: Configure connection</h3>
<ol>
<li>
<p>
Now go to SSIS package or ODBC data source and use previously copied values in <strong>User Account</strong> authentication configuration:
</p>
<ul>
<li>In the <strong>Service Account Email</strong> field paste the service account <strong>Email address</strong> value you copied in the previous step.</li>
<li>In the <strong>Service Account Private Key Path (i.e. *.p12)</strong> field use downloaded certificate's file path.</li>
</ul>
</li>
<li>
<p>
Press <strong>Generate Token</strong> button to generate Access and Refresh Tokens.
</p>
</li>
<li>
<p>
Open your Google Sheets spreadsheet in the browser, e.g.:
<code>https://docs.google.com/spreadsheets/d/1QM6WmGloHeGCliCqV3nEj8P-R1RbS59t5xJutN99rU/edit</code>.
</p>
</li>
<li>
<p>
Extract the spreadsheet Id from the URL and copy it to the clipboard, e.g. <code>1QM6WmGloHeGCliCqV3nEj8P-R1RbS59t5xJutN99rU</code>.
</p>
</li>
<li>
<p>
Go back to authentication configuration window and paste it into <strong>Default SpreadsheetId</strong> field.
</p>
</li>
<li>
<p>
Finally, click <strong>Test Connection</strong> to confirm the connection is working.
</p>
</li>
<li>
<p>
Done! Now you are ready to use Google Sheets Connector!
</p>
</li>
</ol>
]]>
</Notes>
</Auth>
</Auths>
<Template>
<EndPoint>
<Params>
<Param Name="SpreadSheetId" Desc="Enter unique spreadsheet Id. To obtain sheet Id. Open spreadsheet in browser and check URL. You will example value like 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms that is your sheet id." Options="Enter Your Sheet Id=;Example Sheet Id=1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"/>
</Params>
</EndPoint>
<EndPoint Name="tp_sheet">
<Params>
<Param Name="TabName" Desc="If you have multiple tabs on your spreadsheet then you can specify here. By default first tab is used if you do not enter." OptionsEndPoint="get_sheet_properties" OptionEndPointParameters="SpreadSheetId=[$SpreadSheetId$]" OptionsEndPointValueColumn="properties.title" OptionsEndPointLabelColumn="properties.title" Value=""/>
<Param Name="valueRenderOption" Value="FORMATTED_VALUE" Options="FORMATTED_VALUE;UNFORMATTED_VALUE;FORMULA"/>
<Param Name="valueInputOption" Value="USER_ENTERED" Options="USER_ENTERED;RAW" Optional="True"/>
<Param Name="majorDimension" Value="DIMENSION_UNSPECIFIED" Options="DIMENSION_UNSPECIFIED;COLUMNS;ROWS"/>
<Param Name="dateTimeRenderOption" Value="SERIAL_NUMBER" Options="SERIAL_NUMBER;FORMATTED_STRING"/>
</Params>
</EndPoint>
</Template>
<EndPoints>
<EndPoint Name="read_sheet_data" Template="tp_sheet" Label="Read Sheet Data" Desc="Read google spreadsheet data from desired tab / cell location (i.e. start/end range)"
HelpLink="https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get"
Url="/[$SpreadSheetId$]/values/'[$TabName$]'![$Range$]?valueRenderOption=[$valueRenderOption$]&majorDimension=[$majorDimension$]&dateTimeRenderOption=[$dateTimeRenderOption$]" Method="GET"
Filter="$.values[*]"
>
<Params>
<Param Name="Range" Required="True" Value="A1:GR"/>
<Param Name="IncludeParentColumns" Type="Property" Value="False" />
<Param Name="ArrayTransformType" Type="Property" Value="TransformColumnslessArray" Options="Auto Detect Columns Mode=TransformColumnslessArray;Use Custom Columns Mode=TransformSimpleTwoDimensionalArray" />
<Param Name="ArrayTransEnableCustomColumns" Label="First Row Has Column Names" Type="Property" Value="True" Desc="Detect column names based on first row in supplied range." />
<Param Name="ArrayTransCustomColumns" Type="Property" Value="Col1,Col2,Col3" Desc="If auto detection of columns is not possible then use ArrayTransformType=TransformSimpleTwoDimensionalArray and supply custom column list (comma seperated here) here (i.e. Col1,Col2,Col3)." VisibleIf="ArrayTransformType==TransformSimpleTwoDimensionalArray & ArrayTransEnableCustomColumns" />
</Params>
</EndPoint>
<EndPoint Name="clear_sheet_range" Template="tp_sheet" Label="Clear Sheet Values" Desc="Clears values from a spreadsheet. The caller must specify the spreadsheet ID and range. Only values are cleared -- all other properties of the cell (such as formatting, data validation, etc..) are kept."
HelpLink="https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/clear"
Url="/[$SpreadSheetId$]/values/'[$TabName$]'![$Range$]:clear" Method="POST" >
<Params>
<Param Name="Range" Required="True" Value="A2:GR100"/>
</Params>
<OutputColumns>
<Column Name="spreadsheetId" DataType="DT_WSTR" Length="100"/>
<Column Name="clearedRange" DataType="DT_WSTR" Length="255"/>
</OutputColumns>
</EndPoint>
<EndPoint Name="delete_sheet_rows_columns" Label="Delete Sheet Rows / Columns" Desc="Deletes rows / columns based on startIndex / endIndex range (Index is zero based). For example to delete First 5 rows specify startIndex=0 and endIndex=4"
HelpLink="https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate"
Url="/[$SpreadSheetId$]:batchUpdate" Method="POST" ContentType="application/json">
<Body>
<![CDATA[{
"requests": [
{
"deleteDimension": {
"range": {
"sheetId": [$TabId$],
"dimension": "[$Dimension$]",
"startIndex": [$StartIndex$],
"endIndex": [$EndIndex$]
}
}
}
],
"includeSpreadsheetInResponse": false,
"responseIncludeGridData": false
}]]>
</Body>
<Params>
<Param Name="Dimension" Label="Range Type" Required="True" Value="ROWS" Desc="Specifies what would you like to delete (i.e. Rows or Columns)" Options="ROWS;COLUMNS"/>
<Param Name="StartIndex" Label="Range Start Index (starts from 0)" Required="True" Value="0" Desc="Range Start Index (Zero based) for operation. For Column Operation translate Alpha value to zero based index. Example: Column D = Index 3. Column AA=26" />
<Param Name="EndIndex" Label="Range End Index (starts from 0)" Required="True" Value="1" Desc="Range End Index (Zero based) for operation. For Column Operation translate Alpha value to zero based index. Example: Column D = Index 3. Column AA=26" />
<Param Name="TabId" Required="True" Value="0" Desc="Numeric ID of SpreadSheet Tab for which you like to delete rows/columns. For first tab its 0. You can find this ID from Sheet URL in browser. Get Number after edit#gid= (thats your Tab ID)" OptionsEndPoint="get_sheet_properties" OptionEndPointParameters="SpreadSheetId=[$SpreadSheetId$]" OptionsEndPointValueColumn="properties.sheetId" OptionsEndPointLabelColumn="properties.title" />
</Params>
<OutputColumns>
<Column Name="spreadsheetId" DataType="DT_WSTR" Length="100"/>
</OutputColumns>
</EndPoint>
<EndPoint Name="delete_sheet_tab" Label="Delete SpreadSheet Tab" Desc="Deletes specified tab from spreadsheet"
HelpLink="https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate"
Url="/[$SpreadSheetId$]:batchUpdate" Method="POST" ContentType="application/json">
<Body>
<![CDATA[{
"requests": [
{
"deleteSheet": {
"sheetId": [$TabId$]
}
}
],
"includeSpreadsheetInResponse": false,
"responseIncludeGridData": false
}]]>
</Body>
<Params>
<Param Name="TabId" Required="True" Value="0" Desc="Numeric ID of SpreadSheet Tab for which you like to delete rows/columns. For first tab its 0. You can find this ID from Sheet URL in browser. Get Number after edit#gid= (thats your Tab ID)" OptionsEndPoint="get_sheet_properties" OptionEndPointParameters="SpreadSheetId=[$SpreadSheetId$]" OptionsEndPointValueColumn="properties.sheetId" OptionsEndPointLabelColumn="properties.title" />
</Params>
<OutputColumns>
<Column Name="spreadsheetId" DataType="DT_WSTR" Length="100"/>
</OutputColumns>
</EndPoint>
<EndPoint Name="create_sheet_tab" Label="Create SpreadSheet Tab" Desc="Creates new tab for specified spreadsheet."
HelpLink="https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate"
Url="/[$SpreadSheetId$]:batchUpdate" Method="POST" ContentType="application/json">
<Body>
<![CDATA[{
"requests": [
{
"addSheet": {
"properties": {
"title": "[$NewTabName$]",
"gridProperties": {
"rowCount": [$InitialRowCount$],
"columnCount": [$InitialColumnCount$]
},
"tabColor": {
"red": [$TabColorRedValue$],
"green": [$TabColorGreenValue$],
"blue": [$TabColorBlueValue$]
}
}
}
}
]
}]]>
</Body>
<Params>
<Param Name="NewTabName" Required="True" Value="My New Tab" Desc="Name for new Tab you like to add" />
<Param Name="InitialRowCount" Required="True" Value="100" Desc="Initial row count for new sheet" />
<Param Name="InitialColumnCount" Required="True" Value="27" Desc="Initial column count for new sheet" />
<Param Name="TabColorRedValue" Required="True" Value="0.0" Desc="Tab Color - Red Value for RGB" />
<Param Name="TabColorGreenValue" Required="True" Value="0.0" Desc="Tab Color - Green Value for RGB" />
<Param Name="TabColorBlueValue" Required="True" Value="0.0" Desc="Tab Color - Blue Value for RGB" />
</Params>
<OutputColumns>
<Column Name="spreadsheetId" DataType="DT_WSTR" Length="100"/>
</OutputColumns>
</EndPoint>
<EndPoint Name="batch_update_request" Label="Execute Command(s)" Desc="Perform various operations available in online editor such as copy, paste, format, merge. Add multiple JSON command by adding comma after first command (e.g. requests : [ {..cmd1..} , {..cmd2..}, {..cmd3..} ] ) Refer to example requests on help link."
HelpLink="https://developers.google.com/sheets/api/samples/formatting"
Url="/[$SpreadSheetId$]:batchUpdate" Method="POST" ContentType="application/json" Body="[$Body$]" >
<Params>
<Param Name="Body" Editor="MultiLine" Label="Request Body" Required="True" Value="{
"requests": [
{
"deleteDimension": {
"range": {
"sheetId": 0,
"dimension": "ROWS",
"startIndex": 0,
"endIndex": 3
}
}
}
]
}"/>
<Param Name="TabId" Required="True" Value="0" Desc="Numeric ID of SpreadSheet Tab for which you like to delete rows/columns. For first tab its 0. You can find this ID from Sheet URL in browser. Get Number after edit#gid= (thats your Tab ID)" OptionsEndPoint="get_sheet_properties" OptionEndPointParameters="SpreadSheetId=[$SpreadSheetId$]" OptionsEndPointValueColumn="properties.sheetId" OptionsEndPointLabelColumn="properties.title" />
</Params>
<OutputColumns>
<Column Name="spreadsheetId" DataType="DT_WSTR" Length="100"/>
</OutputColumns>
</EndPoint>
<EndPoint Name="update_sheet_data" Template="tp_sheet" Label="Update Sheet Data (Overwrite Cells)"
Desc="Update data to spreadsheet on desired tab / cell location (i.e. start/end range). Limit is 10MB per request."
HelpLink="https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update"
Url="/[$SpreadSheetId$]/values/'[$TabName$]'![$WriteRange$]?valueInputOption=[$valueInputOption$]"
Method="PUT"
MetaDetectionOrder="StaticDynamicVirtual"
BatchSize="10000000"
>
<Body>
<![CDATA[{
"majorDimension": "[$majorDimension$]",
"values": {$rows$}
}]]>
</Body>
<Params>
<Param Name="Range" Label="Range for Table Boundary (Including Header)" Desc="Enter Range to detect Table column names and datatypes. Example: enter A1:C if you have table of 3 columns starting from Cell A1" Required="True" Value="A1:GR"/>
<Param Name="WriteRange" Label="Range for Data Cells" Desc="Cell Range which needs to be update. Example: Enter A2 if you like to start update from cell A2" Required="True" Value="A2:GR"/>
<Param Name="JsonOutputFormat" Required="True" Label="Update Mode" Desc="This mode is used to include or exclude column names in first row. If you include column names then adjust for [Range for Data Cells]" Type="Property" Value="Array2D" Options="Update Values Only=Array2D;Update Values And Header Row=Array2DWithHeader;" />
<Param ForMeta="True" Name="ArrayTransformType" Type="Property" Value="TransformColumnslessArray" Options="Auto Detect Columns Mode=TransformColumnslessArray;Use Custom Columns Mode=TransformSimpleTwoDimensionalArray" />
<Param ForMeta="True" Name="ArrayTransEnableCustomColumns" Type="Property" Value="True" />
<Param ForMeta="True" Name="ArrayTransCustomColumns" Type="Property" Value="Col1,Col2,Col3" Desc="If auto detection of columns is not possible then use ArrayTransformType=TransformSimpleTwoDimensionalArray and supply custom column list (comma seperated here) here (i.e. Col1,Col2,Col3)." VisibleIf="ArrayTransformType==TransformSimpleTwoDimensionalArray & ArrayTransEnableCustomColumns" />
</Params>
<OutputColumns>
<Column Name="spreadsheetId" DataType="DT_WSTR" Length="100"/>
<Column Name="updatedRange" DataType="DT_WSTR" Length="255"/>
<Column Name="updatedRows" DataType="DT_I4" />
<Column Name="updatedColumns" DataType="DT_I4"/>
<Column Name="updatedCells" DataType="DT_I4"/>
</OutputColumns>
</EndPoint>
<EndPoint Name="insert_sheet_data" Template="tp_sheet" Label="Insert Sheet Data (Append at the end)"
Desc="Append data to spreadsheet on desired tab. Data is appended after last non-empty row/cell location. You can supply cell offset to start scan to find non-empty cell."
HelpLink="https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append"
Url="/[$SpreadSheetId$]/values/'[$TabName$]'![$Range$]:append?valueInputOption=[$valueInputOption$]"
Method="POST" ContentType="application/json"
MetaDetectionOrder="StaticDynamicVirtual"
JsonRowFormat="Array2D"
BatchSize="10000"
MaxRequestSize="10MB"
>
<Body>
<![CDATA[{
"majorDimension": "[$majorDimension$]",
"values": {$rows$}
}]]>
</Body>
<Params>
<Param Name="Range" Required="True" Value="A1:GR" Label="Start Range" Desc="This is the range in which next row location is detected based on first empty cell and row (Examples: A1, A1:GR, A1:GR200). This range is also used to detect metadata. Values are appended after the last row of the table."/>
<Param ForMeta="True" Name="ArrayTransformType" Type="Property" Value="TransformColumnslessArray" Options="Auto Detect Columns Mode=TransformColumnslessArray;Use Custom Columns Mode=TransformSimpleTwoDimensionalArray" />
<Param ForMeta="True" Name="ArrayTransEnableCustomColumns" Type="Property" Value="True" />
<Param ForMeta="True" Name="ArrayTransCustomColumns" Type="Property" Value="Col1,Col2,Col3" Desc="If auto detection of columns is not possible then use ArrayTransformType=TransformSimpleTwoDimensionalArray and supply custom column list (comma seperated here) here (i.e. Col1,Col2,Col3)." VisibleIf="ArrayTransformType==TransformSimpleTwoDimensionalArray & ArrayTransEnableCustomColumns" />
</Params>
<OutputColumns>
<Column Name="spreadsheetId" DataType="DT_WSTR" Length="100"/>
<Column Name="tableRange" DataType="DT_WSTR" Length="255"/>
<Column Name="updates.updatedRange" Label="updatedRange" DataType="DT_WSTR" Length="255"/>
<Column Name="updates.updatedRows" Label="updatedRows" DataType="DT_I4" />
<Column Name="updates.updatedColumns" Label="updatedColumns" DataType="DT_I4"/>
<Column Name="updates.updatedCells" Label="updatedCells" DataType="DT_I4"/>
</OutputColumns>
</EndPoint>
<EndPoint Name="get_sheet_properties" Label="Get Sheet Properties / Tab information" Desc="Read read sheet properties (e.g. list of tabs on sheet and its properties)" HelpLink="https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get"
Url="/[$SpreadSheetId$]" Method="GET"
Filter="$.sheets[*]" >
<Params>
<Param Name="SpreadSheetId" Required="True" Value="" Options="Enter SpreadSheet Id=;Example SpreadSheet Id=1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"/>
<Param Name="IncludeParentColumns" Type="Property" Value="False" />
</Params>
</EndPoint>
</EndPoints>
<Tables>
<Table Name="Sheets" Desc="Read, insert, update sheet data for a specified SpreadSheetId and Tab"
LookupEndPoint="read_sheet_data"
SelectEndPoint="read_sheet_data"
InsertEndPoint="insert_sheet_data"
UpdateEndPoint="update_sheet_data"
/>
<Table Name="SheetDetails" Desc="Read sheet information (i.e. tabs on a sheet) for a specified SpreadSheetId"
LookupEndPoint="get_sheet_properties"
SelectEndPoint="get_sheet_properties" />
<Table Name="[$parent.properties.title$]"
Expand="True"
LookupEndPoint="read_sheet_data"
SelectEndPoint="read_sheet_data"
InsertEndPoint="insert_sheet_data"
UpdateEndPoint="update_sheet_data"
DataEndPoint="get_sheet_properties" >
<Params>
<Param Name="TabName" Value="[$parent.properties.title$]" Hidden="True"/>
</Params>
</Table>
</Tables>
<Examples>
<Example Group="ODBC" Label="Query from default Spreadsheet" Code="SELECT * FROM [Sheet1]" Desc="Gets data from Tab name Sheet1 from SpreadSheet Id defined in the connection" ></Example>
<Example Group="ODBC" Label="Query from User defined Spreadsheet" Code="SELECT * FROM [Class Data] WITH(SpreadSheetId='1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms')" Desc="Gets data from Tab name 'Class Data' from user defined SpreadSheet Id" ></Example>
<Example Group="ODBC" Label="Query from custom cell range" Desc="In this example we query Tab name 'Class Data' and we are reading Range 'A4:GR'.">
<Code>
<![CDATA[SELECT * FROM [Class Data]
WITH(
Range='A4:GR' --cell range you like to query
, ArrayTransEnableCustomColumns='False' --do not treat first row in range as Column Names
, SpreadSheetId='1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms' --enter sheet id you like to query. Comment this if you like to use default ID defined in the connection
)]]>
</Code>
</Example>
<Example Group="ODBC" Label="Query from custom cell range" Desc="In this example we query Tab name 'Class Data' and we are reading Range 'A4:GR'.">
<Code>
<![CDATA[
UPDATE [Sheet1]
SET Col1='data-1', Col2=100, Col3='2020-01-31' --column names are ignored. Values are sent in the same order you supply and writtern to start cell specified by WriteRange
WITH(
, WriteRange='G9' --start writing from here
, SpreadSheetId='1az2H8ZYk7BvjddVTqPR-LfDjX9IRpIpjCDpFPe9EzkU' --comment this to use default Sheet Id from connection setting
)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Update Multiple Rows in Sheet from CSV file" Desc="In this example we query CSV file as Source (Using ZapyySys CSV ODBC Driver) and updating Google Sheet in BULK.">
<Code>
<![CDATA[
UPDATE [Sheet1]
SOURCE(
'ODBC', --driver type ODBC | MSSQL | OLEDB
'Driver={ZappySys CSV Driver};', --connection string for driver
'SELECT * FROM $ WITH (SRC=''c:\data.csv'') ' --sql query for source data
)
WITH(
WriteRange='G9', SpreadSheetId='1az2H8ZYk7BvjddVTqPR-LfDjX9IRpIpjCDpFPe9EzkU' --comment this to use default Sheet Id from connection setting
)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Update Values Vertically (Column Mode)" Desc="In this example we will write value as columns rather than row.">
<Code>
<![CDATA[
UPDATE [Sheet1]
SET Col1='Jan',Col2='Feb',Col3='Mar'
WITH(
WriteRange='G9',
SpreadSheetId='1az2H8ZYk7BvjddVTqPR-LfDjX9IRpIpjCDpFPe9EzkU', --comment this to use default Sheet Id from connection setting
MajorDimension='COLUMNS' --write values vertical rather horizontal
)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Insert Data in Sheet1" Code="INSERT INTO "Sheet1"("MyStringCol", "MyIntegerCol", "MyDateCol", "MyDecimalCol") VALUES('AAA',100,'2020-01-01',150.33)" Desc="Insert row to tab name Sheet1 in SheetId defined in connection" ></Example>
<Example Group="ODBC" Label="Insert Multiple Rows in Sheet1 from CSV file" Desc="In this example we query CSV file as Source (Using ZapyySys CSV ODBC Driver) and updating Google Sheet in BULK.">
<Code>
<![CDATA[
INSERT INTO [Sheet1]
SOURCE(
'ODBC', --driver type ODBC | MSSQL | OLEDB
'Driver={ZappySys CSV Driver};', --connection string for driver
'SELECT * FROM $ WITH (SRC=''c:\temp\dump.txt'') ' --sql query for source data
)
WITH(
Range='G9', SpreadSheetId='1az2H8ZYk7BvjddVTqPR-LfDjX9IRpIpjCDpFPe9EzkU' --comment this to use default Sheet Id from connection setting
)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Write Values Vertically (Column Mode)" Desc="In this example we will write value as columns rather than row.">
<Code>
<
VALUES('Jan','Feb','Mar')
WITH(
Range='G9', --starting cell to write data
SpreadSheetId='1az2H8ZYk7BvjddVTqPR-LfDjX9IRpIpjCDpFPe9EzkU', --comment this to use default Sheet Id from connection setting
MajorDimension='COLUMNS' --write values vertical rather horizontal
)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Execute Action (i.e. Delete Rows / Columns)" Desc="This example shows how to execute various commands for sheet (i.e. copy, paste, formatting, delete, merge etc). In this example we are executing delete comamnds (i.e. deleteDimension commands). Notice we called same command twice becuase we want to delete two ranges (index 10-20 and 50-60). You can execute any valid command available by Google Sheets API. Here are some good examples of formatting commands https://developers.google.com/sheets/api/samples/formatting">
<Code>
<![CDATA[
SELECT * FROM batch_update_request
WITH(
Body='{
"requests": [
{
"deleteDimension": {
"range": {
"sheetId": 0,
"dimension": "ROWS",
"startIndex": 10,
"endIndex": 20
}
}
} ,
{
"deleteDimension": {
"range": {
"sheetId": 0,
"dimension": "ROWS",
"startIndex": 50,
"endIndex": 60
}
}
}
]
}'
, TabId='0' -- tab internal id (use UI to get this). 0 means first tab. Or check URL in browser and see at the end of URL #gid=xxxxxxx where xxxxxx is your tab id
, SpreadSheetId='1az2H8ZYk7BvjddVTqPR-LfDjX9IRpIpjCDpFPe9EzkU' --comment this to use default Sheet Id from connection setting
)
]]>
</Code>
</Example>
</Examples>
</ApiConfig>
