Qiang Blog

Just another zhangjingqiang's blog.

使用Google API自动把本地文件内容写入Google Spreadsheet

需要使用的Google API

  • Google Drive API
  • Google Spreadsheet API

实现的功能

  • 获取Google认证权限
  • 在Google Drive创建Google Spreadsheet
  • 分享给用户,域名功能
  • 删除原有sheet1并创建新的固定sheet
  • 向Google Spreadsheet循环创建并写入本地指定路径下所有文件内容
  • 设置sheet头部样式(标题,颜色,固定,加粗等)

此例文件类型

  • tsv

使用方法

Step 1

从Google开发者页面下载 client_secret.json
参考:

https://developers.google.com/sheets/api/quickstart/python

Step 2

在terminal运行:

$ python write_to_google_sheets.py <file_name> <folder_path>

精彩代码

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from __future__ import print_function
import httplib2
import os
import sys
import json
import re
from termcolor import colored

from apiclient import discovery
from oauth2client import client
from oauth2client import tools
from oauth2client.file import Storage
import requests

reload(sys)
sys.setdefaultencoding('utf8')

# If modifying these scopes, delete your previously saved credentials
# at ~/.credentials/sheets.googleapis.com-python-quickstart.json
SCOPES = "https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/spreadsheets"
CLIENT_SECRET_FILE = 'client_secret.json'
APPLICATION_NAME = 'Google API Drive + Spreadsheet'

def get_credentials():
    """Gets valid user credentials from storage.

    If nothing has been stored, or if the stored credentials are invalid,
    the OAuth2 flow is completed to obtain the new credentials.

    Returns:
        Credentials, the obtained credential.
    """
    home_dir = os.path.expanduser('~')
    credential_dir = os.path.join(home_dir, '.credentials')
    if not os.path.exists(credential_dir):
        os.makedirs(credential_dir)
    credential_path = os.path.join(credential_dir,
                                   'sheets.googleapis.com-python-quickstart.json')

    store = Storage(credential_path)
    credentials = store.get()
    if not credentials or credentials.invalid:
        flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
        flow.user_agent = APPLICATION_NAME
        if flags:
            credentials = tools.run_flow(flow, store, flags)
        else: # Needed only for compatibility with Python 2.6
            credentials = tools.run(flow, store)
        print('Storing credentials to ' + credential_path)
    return credentials

def main():
    """Create spreadsheet and write data into it
    Use:
    - Google Drive API
    - Google Spreadsheet API
    """
    credentials = get_credentials()
    http = credentials.authorize(httplib2.Http())

    # Create new spreadsheet
    spreadsheet_id = create_spreadsheet(http)

    # Create new sheet
    write_data_to_sheets(http, spreadsheet_id)

    print(colored('Finish!', 'green'))

def create_spreadsheet(http):
    drive_service = discovery.build('drive', 'v3', http=http)
    file_metadata = {
      'name' : sys.argv[1],
      'mimeType' : 'application/vnd.google-apps.spreadsheet'
    }
    file = drive_service.files().create(body=file_metadata, fields='id').execute()
    # share spreadsheet
    share(drive_service, file.get('id'))
    return file.get('id')

def write_data_to_sheets(http, spreadsheet_id):
    discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
                    'version=v4')
    spreadsheet_service = discovery.build('sheets', 'v4', http=http,
                              discoveryServiceUrl=discoveryUrl)

    files = os.listdir(sys.argv[2])
    print(colored(files, 'red'))

    # create new sheets and delete default sheet1
    spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=base_sheet()).execute()
    # write log files to sheets
    for file in files:
        write_log_to_sheet(spreadsheet_service, spreadsheet_id, file)

def base_sheet():
    data = {
      'requests': [
        {
          'addSheet':{
            'properties': {'title': u'New Sheet Name'}
          }
        },
        {
          'deleteSheet':{
            'sheetId': 0
          }
        }
      ]
    }
    return data

def write_log_to_sheet(spreadsheet_service, spreadsheet_id, file):
    # make sheet
    body = {
      'requests': [
        {
          'addSheet':{
            'properties': {'title': u'{0}'.format(file)}
          }
        }
      ]
    }
    result = spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()
    sheetId = result['replies'][0]['addSheet']['properties']['sheetId']

    # write to sheet
    values = []
    values.append(titles(file))
    lines = [line.rstrip() for line in open(file)]
    for i in range(len(lines)):
        values.append(re.split(r'\t+', lines[i]))
    data = [
            {
                'range': '{0}!A1'.format(file),
                'values': values
            }
    ]
    body = {
            'valueInputOption': 'USER_ENTERED',
            'data': data
    }
    spreadsheet_service.spreadsheets().values().batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()

    # format header row
    body = {
    "requests": [
    {
      "repeatCell": {
        "range": {
          "sheetId": sheetId,
          "startRowIndex": 0,
          "endRowIndex": 1
        },
        "cell": {
          "userEnteredFormat": {
            "backgroundColor": {
              "red": 0.0,
              "green": 0.0,
              "blue": 1.0
            },
            "horizontalAlignment" : "LEFT",
            "textFormat": {
              "foregroundColor": {
                "red": 1.0,
                "green": 1.0,
                "blue": 1.0
              },
              "fontSize": 12,
              "bold": 'true'
            }
          }
        },
        "fields": "userEnteredFormat(backgroundColor,textFormat,horizontalAlignment)"
      }
    },
    {
      "updateSheetProperties": {
        "properties": {
          "sheetId": sheetId,
          "gridProperties": {
            "frozenRowCount": 1
          }
        },
        "fields": "gridProperties.frozenRowCount"
      }
    }
    ]
    }
    spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()

def titles(file):
    values_title = [
        'ID', 'Name', 'Description'
    ]
    return values_title

def share(drive_service, spreadsheet_id):
    batch = drive_service.new_batch_http_request(callback=callback)
    # share to users
    user_permission = [
        'user1@example.com',
        'user2@example.com'
    ]
    for user in user_permission:
        share_user(drive_service, spreadsheet_id, batch, user)
    # share to domains
    domain_permission = [
        'google.com',
        'facebook.com'
    ]
    for domain in domain_permission:
        share_domain(drive_service, spreadsheet_id, batch, domain)
    # batch execute
    batch.execute()

def share_user(drive_service, spreadsheet_id, batch, user):
    user_permission = {
            'type': 'user',
            'role': 'writer',
            'emailAddress': user
            }
    batch.add(drive_service.permissions().create(
        fileId=spreadsheet_id,
        body=user_permission,
        fields='id',
        ))
    return batch

def share_domain(drive_service, spreadsheet_id, batch, domain):
    domain_permission = {
            'type': 'domain',
            'role': 'reader',
            'domain': domain
            }
    batch.add(drive_service.permissions().create(
        fileId=spreadsheet_id,
        body=domain_permission,
        fields='id',
        ))
    return batch

def callback(request_id, response, exception):
    if exception:
        print(exception)
    else:
        print("Permission Id: {0}".format(response.get('id')))

if __name__ == '__main__':
    if len(sys.argv) == 3 and os.path.isdir(sys.argv[2]):
        main()
    else:
        print('Please input a file name and log path.')

google-drive-api google-spreadsheet-api python