Dataframe to Google Sheet
Published: 2021-01-15 13:43:03

## Setup in Google Console
* Create a new project in [Google Console](http://console.developers.google.com).
* You have to enable google services in order to get access to google sheet. Under **API & Services** Click on `Enable API and Services`
    <ul>
        <li>Enable `Google Drive API`</li>
        <li>Enable `Google Sheets API`</li>
    </ul>
* Now go to **Credentials** menu and click on `Manage service accounts`.
    <ul>
        <li>Click on `Create Service Account`</li>
        <li>Optionally you can also add roles but we are going to skip that since we need to access files for different users.</li>
         <li>Inside **Service Account Details** click on `Add key`. Download it and store it we will need it later while setting up functionality in jupyter.</li>
    </ul>

## Setup in Jupyter
* Place the service key in the root of your jupyter folder. (You can change the path too, but you have to modify it inside the function.)
* To get access of google drive and google sheet we need to install some libraries. Install the following libraries:

```
    pip install pandas gspread google-api-python-client
```

* The following function takes `dataframe` and two optional arguments `sheet_name` and `email`. `sheet_name` will rename the sheet to a given name and `email` will add permission to add the created file into the given account's google drvive. Copy the following function: 

```
    import pandas as pd
    from numpy.random import randint
    import gspread
    from google.oauth2 import service_account
    from googleapiclient.discovery import build

    def df_to_sheet(df, sheet_name, email_address):
        """
        Connects with google drive. Creates a sheet and copy data from dataframe, stores and  create a link.

            :param df: dataframe you want to store.
            :param sheet_name: Name of the sheet you want to save in google drive.
            :param email_address: of the user of google drive.
            :return: returns URL of the file saved 
        """

        scopes = [ "https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive"]
        credentials = service_account.Credentials.from_service_account_file("./credentials.json",scopes = scopes)
        permission = { "role": 'writer', "type": 'user', "emailAddress": email_address}

        try:
            # connecting to sheets and google drive
            sheet_service = gspread.authorize(credentials) 
            drive_service = build("drive", "v3", credentials=credentials)

            # creating sheet and copying data in worksheet
            sh = sheet_service.create(sheet_name)
            worksheet = sh.get_worksheet(0)
            worksheet.update([df.columns.values.tolist()]+df.values.tolist())
            drive_service.permissions().create(fileId=sh.id, body=permission).execute()

        except Exception as e: 
            print('Error creating google sheet: ',e)
            return None
        return "https://docs.google.com/spreadsheets/d/%s" % sh.id
```
* Now you can create and store any dataframe you want. **Note:** make sure there are no null values inside dataframe `df.fillna('',inplace=True)`.
```
    ## creating a dataframe
    df = pd.DataFrame({'x':randint(5, size=10),'y':randint(5, size=10)})

    ## storing dataframe to google sheet 
    df_to_sheet(df, sheet_name='New Sheet', email_address='ahmadabdullah247@gmail.com')
```