Untitled

mail@pastecode.io avatar
unknown
python
2 years ago
4.8 kB
4
Indexable
Never
import datetime as dt
from google.oauth2.service_account import Credentials
from googleapiclient import discovery

SCOPES = [
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive'
]

FORMAT = "%Y/%m/%d %H:%M:%S"

# Добавьте почту шефа.
EMAIL_USER = 'lonely.rover.io@gmail.com'

# Введите учетные данные шефа.
info = {
  "type": "service_account",
  "project_id": "chef-project",
  "private_key_id": "ae966c09408cd809ff4ce5b90159ca9a81b4dfd3",
  "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEuwIBADANBgkqhkiG9w0BAQEFAASCBKUwggShAgEAAoIBAQCj71Jq/QP7toLH\nAknNQZOclheRw4jMIkc1RoJX5Z5WMl7RFUsf8dXNGV0xR+3uLmhvrJ6EZCs3l0x/\nDk4B1VDLTrZUOemEwKYQPh8wvPlKjh6hMMrYnI5Pclg4TRaiT8AWwTYyv0HGAWa5\nk3VXA6E6YJVu654P5mG5B5+MNVQIwUrt0Md8awyP0Luqr9DrQWO92OhTO2x21W/B\nFZTDRmOknruU44R7TDyg2ZorxQk6pbHYnWitXWY5Ay2JJem1mQSR6Qt1oJQWr6LE\nkKr3M1cJxHJpz8E2Wlk43Ih5g2WuWKaBdGd6NTlHjcF23CS1EQOf8LLzdEZ8T7is\n3wgA4mgnAgMBAAECgf8BYy/PYqol9zrOybYrho05RgvtBTqjKlHFTFN4wE4WwgEL\nbqK0IoKCDzTlYIvNouw/Wwv8aDH1cSe0lj+9buYVfRUN9sTYhXONBPmT8AWBcV0m\nVW4MvJvNEFVZefg3EiBjZj852WSKcyNAxClkvc3KMLG9vpYX4bCctZ55ZqTPbiU8\ncrkuGyKZCMl71VC6ERcK4PMgPVghN9N21i0znUL+QY7uS8W6eY6UvELadejICAIb\nTO7zV92/t8O5VOCFWM/B8F2OU8tqejbNEeI2FmvEYfpidi7CvmG97C0GfjupUcuu\nVudNHGhgQ55fbLUM1qrMIyditEzE6dgyUNVdGTECgYEAzoV8XWzOjjklzB/Sro60\nQT6+xi9MwhuAZlqe7j2TL80+KjdrWeRaZ2QUU0Sxk3fHXCtC890zP6bBHNMhwT5d\ndDD2f3KA2lGY8d+kHqqrMBexGkx/02TtlPtzHn67U2dOcz+PVOYNsPpPlI+a9BzA\niBjmQhgF1/8hpsSaulCL4RcCgYEAyzXjDCPGA8x9d0GSaggFPPjzq3AF4SyVVlM+\nogDe7BBae0dMdnm8bC3rnuCR9rYyRWF9HBHxB/57jb5asA4Al7mBjJ2T2BsbBiXW\nudnHS84xc5qWv9og2aNLvubQ7FSFbkWgAfTndiU+zfXVR0QjHLHVH2JYhVBIBOkF\nrqSye3ECgYAD6r44G2czhVv1KilZzDUZA7KkmaMQD2t5BYD8LcjL4E2siU6M7urU\nCS7lgWkIzf3G/r8PVwOw0l3ZSRvbyW4GVSNnuWk+Osv20nIPbtc+W0StpgPKo6Bp\n8fb1/htZcp0nTG5QguiBkc3OmIas2JrCOg6r9D6wIROnURVKMVrl0wKBgQC6q97N\naRNeq/vZkF/ajVGaiCr3IqoUBquMqF3R3jDp87/anU2NTBkd2zUmNq9/TjFx8LNI\nFOcw68I+HE4cilBbEGsEbEDC9Wtmw120Tm04ospkou62gL9KjipnzIrBfocwHD93\nbK0vp9lAxAJPPDVxB596/BHLLUtrT1AL9XmL0QKBgFe0XXZoyoeyhIS4ZAWpw30P\nDifqdn3j/rRTW7U5Wxl832vBlmOS7/EKbyc/JoypdpLXK9VMDvO1hMz2b/zMC2A3\nRIRIENZX91hkocQ1fujezBYIyLITxFqDFVsca3tUWca6USGv8+4fA7F6/UnDtDF5\n1WswjrILyaQuv1ZA6G1v\n-----END PRIVATE KEY-----\n",
  "client_email": "test@chef-project.iam.gserviceaccount.com",
  "client_id": "101814793754218499111",
  "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/test%40chef-project.iam.gserviceaccount.com"
}

SPREADSHEET_ID = '1jUzxH_XG8WrRGlO3lX2QIlwGAtrMCNgZpTBBbVOtOCY'

# Повторите функцию авторизации в Google Sheets API..
def auth():
    credentials = Credentials.from_service_account_info(
        info=info, scopes=SCOPES)
    service = discovery.build('sheets', 'v4', credentials=credentials)
    return service, credentials


def set_user_permissions(spreadsheet_id, credentials):
    permissions_body = {'type': 'user',
                        'role': 'reader',
                        'emailAddress': EMAIL_USER}

    drive_service = discovery.build('drive', 'v3', credentials=credentials)

    drive_service.permissions().create(
        fileId=spreadsheet_id,
        body=permissions_body,
        fields='id'
    ).execute()


# Напишите функцию, которая получит данные из диапазона Лист1!A1:I10
def read_values(service, spreadsheetId):
    # Формирование запроса к Google Sheets API.
    request = service.spreadsheets().values().get(
        spreadsheetId=spreadsheetId,
        range='Лист1!A1:I10',
    )
    # Выполнение запроса.
    response = request.execute()
    print(response)


def create_spreadsheet(service):
    now_date_time = dt.datetime.now().strftime(FORMAT)
    spreadsheet_body = {
        'properties': {'title': 'Morelia viridis menu', 'locale': 'ru_RU'},
        'sheets': [{'properties': {'sheetType': 'GRID',
                                   'sheetId': 0,
                                   'title': now_date_time,
                                   'gridProperties': {'rowCount': 20, 'columnCount': 11}}}]
    }
    request = service.spreadsheets().create(body=spreadsheet_body)
    response = request.execute()
    spreadsheetId = response['spreadsheetId']
    return spreadsheetId


if __name__ == '__main__':
    service, credentials = auth()
    set_user_permissions(SPREADSHEET_ID, credentials)
    read_values(service, SPREADSHEET_ID)