Gspread to link Google sheets in Python

How to use Gspread

Google User Authentication Settings

Google User Authentication setting is required separately to link Google sheets in Python. 

Access the GCP User Credentials site (without the project) and create a project.

Gspread 

Create 'API User Credentials > Service Account Key' under the project you created Select Create a new service account to specify a name and role, and generate a JSON type key, which will download the JSON file. Later, the JSON file will be imported within Python code for security settings.

• Once you have completed creating user credentials, enter the 'Dashboard' menu in the left menu and enter the '+ API and service usage settings' menu. Enter the [G Suite - Google Drive API, Google Sheets API] menu on the first screen and click "Enable" to activate API usage.

• If you skip this step, the following error message appears when linking the actual sheet. If you encounter this message, you can enter the link in the "extended Help" section below and activate the API.

APIError: {
 "error": {
  "errors": [
   {
    "domain": "usageLimits",
    "reason": "accessNotConfigured",
    "message": "Access Not Configured. Drive API has not been used in project {your project} before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/drive.googleapis.com/overview?project={your project} then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry.",
    "extendedHelp": "https://console.developers.google.com/apis/api/drive.googleapis.com/overview?project={your project}"
   }
  ],
  "code": 403,
  "message": "Access Not Configured. Drive API has not been used in project {your project} before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/drive.googleapis.com/overview?project={your project} then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry."
 }
}

• Once you're done setting up Google User Authentication, let's set up an environment for actual interworking. Use the terminal to install the oauth2client, gspread, and PyOpenSSL packages. The oauth2client Guide tells you to use Google-auth because there will be no more updates, but gspread doesn't support Google-auth yet, so you have to use the oauth2client. (If you want to try using Google-auth by setting up your Class, get help here.)

pip install --upgrade oauth2client
pip install gspread
pip install PyOpenSSL

If you open the JSON file that you downloaded earlier, it's as follows.

{
  "type": "service_account",
  "project_id": "python-linkage-sample",
  "private_key_id": "...",
  "private_key": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n",
  "client_email": "...@python-linkage-sample.iam.gserviceaccount.com",
  "client_id": "...",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/...%40python-linkage-sample.iam.gserviceaccount.com"
}

• Copy the 'client_email' section to give access to the sheet you want to interlock.
  This is actually the most annoying part. We have to give them access to every sheet we want to link to. -,-
  Authorization is typically the same way you share a sheet. Write 'client_email' instead of mail.

Load the previously downloaded JSON file and link it to Google Sheet.

from oauth2client.service_account import ServiceAccountCredentials
import gspread

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
        '{your JSON key file path}', scope)
gc = gspread.authorize(credentials)

• get_all_values() When code is executed using a function, the values in the form of the list are returned.

gc1 = gc.open("python-linkage-sample").worksheet('sheet1')
gc2 = gc1.get_all_values()
print(gc2)




[['id', 'product', 'price'], ['1', 'book', '1000'], ['2', 'pencil', '100'], ['3', 'pen', '300'], ['4', 'notebook', '500'], ['5', 'eraser', '50']]

3) Update cells

• Specify the cells and insert the updates.

gc1.update_acell('B2', 'novels')




{'spreadsheetId': '1jC0tOnSm4LMMenJbdibxhC2dvxYl8SBmwi8Ne-sKByI', 'updatedRange': "'sheet1'!B2", 'updatedRows': 1, 'updatedColumns': 1, 'updatedCells': 1}​

For more Usage, see gspread Github and gspread Guide. Using R to work with Google sheets is much simpler.