sheet_script/main.py at master · eliyarson/sheet_script · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
import pandas as pd
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import numpy as np

#originKey = '1G5CrpUKkn5H2tA2IvIYjyIASr3UMoGqo4yXBbX7PtHI'
#originSheetName = 'origin_sheet'
#destinySheetName = 'benchmark_sheet'


def sheet_script(request):
    ## Auth
    scope = [
        "https://spreadsheets.google.com/feeds",
        "https://www.googleapis.com/auth/drive",
    ]
    credentials = ServiceAccountCredentials.from_json_keyfile_name(
        "credentials.json", scope
    )
    gc = gspread.authorize(credentials)

    ## POST Request

    content_type = request.headers['content-type']
    if content_type == 'application/json':
        request_json = request.get_json(silent=True)
        if request_json and 'origin_key' and 'origin_sheet_name' and 'destiny_sheet_name' in request_json:
            origin_key = request_json['origin_key']
            origin_sheet_name = request_json['origin_sheet_name']
            destiny_sheet_name = request_json['destiny_sheet_name']
            destiny_key = request_json['destiny_key']
        else:
            raise ValueError(
                    "JSON is invalid")
    else:
        raise KeyError("Content type <> application/json")

    ## Open origin_key Spreadsheet
    origin_spreadsheet = gc.open_by_key(origin_key)
    ## Get origin_sheet_name data
    origin_sheet = origin_spreadsheet.worksheet(origin_sheet_name)
    origin_sheet_data = origin_sheet.get_all_values()

    ## Transform into DataFrame for manipulations
    ##uncomment if you want to do data transformation

    #header = origin_sheet_data[0]
    #df = pd.DataFrame(data=origin_sheet_data, columns=header)
    #df.drop(0, inplace=True)
    #df['d'] = [1, 2, 3]
    #rand = list(np.random.randint(0, 10, size=(1000, 4)))
    #df2 = pd.DataFrame(rand, columns=list('abcd'))
    #df = df.append(df2, ignore_index=True)
    #df_list = [df.columns.values.tolist()] + df.values.tolist()

    ## Copy data
    destiny_spreadsheet = gc.open_by_key(destiny_key)
    destiny_sheet = destiny_spreadsheet.worksheet(destiny_sheet_name)
    destiny_sheet.clear()

    #if you uncomment the section above, comment the line below.
    df_list = origin_sheet_data

    rows = len(df_list)
    cols = len(df_list[0])
    destiny_sheet.resize(rows, cols)
    params = {'valueInputOption': 'RAW'}
    body = {'values': df_list}
    destiny_spreadsheet.values_append(f'{destiny_sheet_name}!A1', params, body)
    request_json['status'] = 'OK'

    return request_json