Setting up a Google spreadsheets connection

Table of contents


Introduction

To start using Google spreadsheets in ZennoPoster, follow these steps.

Generating a new Google API key

Creating a new application

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)

Detailed error text: The developer hasn’t given you access to this app. It’s currently being tested and it hasn’t been verified by Google. If you think you should have access, contact the developer

Solution: the application you have created for accessing the Google API is in test mode and the account from which you are trying to log in is not the creator of this application and is not in the list of test users. You can read how to publish an application or add a user to the list of test users in the same article. For more information, see Publishing a Project.


Useful links: