Setting up a Google spreadsheets connection
- ZL Support
Please read the Terms of Use for Materials on ZennoLab
Table of contents
Introduction
To start using Google spreadsheets in ZennoPoster, follow these steps.
Generating a new Google API key
Creating a new application
Log in to your Google account or create a new one.
Go to Google Cloud Platform. The first time you visit the site, you will need to:
select country (1)
read the rules for using the services and, if you agree with them, accept them by checking the appropriate setting (2)
(optional) agree to be sent by email (3)
at the end press AGREE AND CONTINUE (4)
Select the OAuth consent screen (1) and click CREATE PROJECT (2).
A new project will be created.
Enter any project name in the Project name field (1), but in English only, Location (if necessary) (2) and press CREATE (3).
In the next window select External (1) and press CREATE (2)
In the window that opens, enter any application name in the App name field (1), select an email in the User support email list (2)
Scroll down the page and re-enter your email (3) and click SAVE AND CONTINUE (4)
In the next window (Scopes), simply scroll down the page and click SAVE AND CONTINUE.
In the Test users window, also scroll down and click SAVE AND CONTINUE.
In the new window (Summary), scroll down and press BACK TO DASHBOARD.
Publishing a project
Testing mode
In this mode, every 7 days it will be necessary to re-authorize in the application (for May 2021. Subsequently, Google may change this period). Link to Google Help.
The number of test users is limited - there can be no more than 100 of them!
After adding a user to the list of testers, it is no longer possible to remove him from there!
You can leave the application in test mode. Then it will be available only for the creator account and users who are added to the Test Users list, for this:
on the OAuth consent screen, scroll down a bit and in the Test users section, click the + ADD USERS button.
in the window that opens, add the email address of the required account and click the SAVE button.
Publish App
You can also publish the application. Then it will be available to all users who have a Google account.
In the window that opens, click the PUBLISH APP button
CONFIRM in pop-up window
Creation of credentials
In the Credentials section (1) click CREATE CREDENTIALS (2) and select OAuth client ID (3)
Select the Desktop app item in the Application type drop-down list (1) and click CREATE (2)
A new OAuth client created window will open, click OK in it.
After that, click either on the name of the newly created application or on the icon for editing it
In the window that opens, you need to download the key as a file, to do this, click the DOWNLOAD JSON button.
Enabling the use of the Google Sheets and Drive API
There are very few Google spreadsheets left for the correct work!
To enable the Google spreadsheets API, follow this link.
Select your project (1) and press ENABLE (2).
To enable the Google Drive API, follow this link.
Select your project (1) and press ENABLE (2).
Adding a key to the program
Open Google spreadsheets Settings (Edit => Settings => Google Spreadsheets)
Click […] and select the credential file (1), click Connect (2).
After that, a browser window will open, where you will need to log into your Google account where you created the key.
Since you have just created your application, a warning window may appear. Since you trust your application, you need to select Advanced settings (1) and Go to the "Your application" page (2).
After that, allow your new application to access your account data so that you can read and write spreadsheets.
Then again:
After that, the browser will display the message Received verification code. You may now close this window - this means that everything was done correctly! You can close this browser tab.
API request limits
What are the limitations?
There are restrictions on the number of requests: 50,000 requests per day for one project and 10 requests per second for one IP address (these limits are valid for May 2021).
Where can I see the current number of requests made?
You can find this information in the Google Cloud Platform Dashboard (remember to select the correct project).
How are the limits increased?
Information on how to expand the limits can be found at this link - Request an additional quota.
How does ZennoPoster spend limits?
The number of queries, in a simplified form, is influenced by two factors - whether the spreadsheets has changed and whether the loading of third-party changes is enabled (setting the Spreadsheet change processing policy).
If downloading third-party changes is enabled, then every minute a request will be sent to the Drive API to compare spreadsheets versions.
When changing the spreadsheet itself, different types of queries are used, in general, up to 5 queries per spreadsheet per minute. Those. if 10 spreadsheets are actively changing, then there will be a maximum of about 60 requests per minute (Spreadsheets API + Drive API).
Possible mistakes
Authorization failed (Error 403: access_denied)
Useful links: