Skip to content

Instantly share code, notes, and snippets.

@olarclara
Last active April 19, 2021 14:56
Show Gist options
  • Save olarclara/fbbacaf3f7457b224e43ab5e8a2ae192 to your computer and use it in GitHub Desktop.
Save olarclara/fbbacaf3f7457b224e43ab5e8a2ae192 to your computer and use it in GitHub Desktop.
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")
@Seanyap90
Copy link

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?

@olarclara
Copy link
Author

@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"
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment