df-to-gsheets.py

from datetime import date
from google.oauth2 import service_account
from googleapiclient.discovery import build
import pandas as pd
import numpy as np


credentials = service_account.Credentials.from_service_account_file(
    "./credentials.json"
)
scopes = credentials.with_scopes(
    [
        "https://www.googleapis.com/auth/spreadsheets",
        "https://www.googleapis.com/auth/drive",
    ]
)


def create_sheet(title, data):
    sheets_service = build("sheets", "v4", credentials=credentials)
    sheets = sheets_service.spreadsheets()

    create_body = {"properties": {"title": f"{title} {date.today()}"},
                   "sheets": list(map(lambda d: {"properties": {"title": d.get("title")}}, data))}
    res = sheets.create(body=create_body).execute()
    spreadsheet_id = res.get("spreadsheetId")

    def df_to_sheet(df):
        df_columns = [np.array(df.columns)]
        df_values = df.values.tolist()
        df_to_sheet = np.concatenate((df_columns, df_values)).tolist()
        return df_to_sheet

    update_body = {
        "valueInputOption": "RAW",
        "data": list(map(lambda d: {"range": d.get("title"), "values": df_to_sheet(d.get("df"))}, data))
    }

    sheets.values().batchUpdate(spreadsheetId=spreadsheet_id, body=update_body).execute()

    return res


def share_spreadsheet(spreadsheet_id, options, notify=False):
    drive_service = build("drive", "v3", credentials=credentials)

    res = (
        drive_service.permissions()
        .create(
            fileId=spreadsheet_id,
            body=options,
            sendNotificationEmail=notify,
        )
        .execute()
    )

    return res


def create_and_share_spreadsheet(title, data, permissions):
    sheet = create_sheet(title, data)
    share_spreadsheet(sheet.get("spreadsheetId"), options=permissions.get(
        "info"), notify=permissions.get("notify"))

    return sheet.get("spreadsheetUrl")

Hello Maria,

I tried to use your share_spreadsheet function to share the spreadsheet I created using the create_spreadsheet.
I got error 401 - authorisation error, login required.
I passed the following into the share_spreadsheet function

options = [
{
"domain": "gmail.com",
"role": "owner"
}
]

res = share_spreadsheet(spreadsheet_id, options = options)

Do you know how to solve this error?

@Seanyap90

The domain value should be used to give access to a specific domain, for example, the company you work for. If you wish to make the file public, you can use:

{
    "role": owner / organizer / fileOrganizer / writer / commenter / reader
    "type": "anyone"
}