Raise local CSV to Google SpreadSheet

Local CSV data to Google SpreadSheet I wanted to give it exactly, so I made a script for it.

Below is a brief introduction and sample script

Turn on Google Sheets API

Refer to here https://developers.google.com/sheets/api/quickstart/python#step_1_turn_on_the_api_name

Install Google Client Library

Refer to here https://developers.google.com/sheets/api/quickstart/python#step_2_install_the_google_client_library

Create a function for authentication

Leave the get_credentials here https://developers.google.com/sheets/api/quickstart/python#step_3_set_up_the_sample

However, regarding SCOPE, we will edit it, so change it next https://www.googleapis.com/auth/drive

sample

--A function that takes a CSV file, makes it an element for each line, and puts it in a list.

def csv2rows(csv_name):
    df = pd.read_csv(csv_name, index_col=False)
    rows = []
    for index, row in df.iterrows():
        values = []
        for c in row:
            values.append({'userEnteredValue': {'stringValue': str(c)}})
        rows.append({'values': values})
    return rows

--Get the csv file and put it in a spreadsheet

sheet_index = 1
requests = []

rows =  csv2rows('aaa.csv', header=None)
sheet_title = 'aaa'
sheet_id = sheet_index
requests.append(
    { #Creating a sheet
      'addSheet': {
        'properties': {
          'title': sheet_title,
          'index': 0,
          'sheetId': sheet_id
        }
      }
    }
)
requests.append(
    { #Update with the contents of csv that got the created sheet
        'updateCells': {
          'start': {
            'sheetId': sheet_id,
            'rowIndex': 0,
            'columnIndex': 0
          },
          'rows': rows,
          'fields': 'userEnteredValue'
        }
    }
)

credentials = get_credentials()
http = credentials.authorize(httplib2.Http())
discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?version=v4')
service = discovery.build('sheets', 'v4', http=http, discoveryServiceUrl=discoveryUrl)

spreadsheet_id = '${Any SpreadSheetId}'

batch_update_spreadsheet_request_body = {
  'requests': requests
}

#Execution of request
result = service.spreadsheets() \
                          .batchUpdate(spreadsheetId=spreadsheet_id, body=batch_update_spreadsheet_request_body) \
                          .execute()

Recommended Posts

Raise local CSV to Google SpreadSheet
What to do to get google spreadsheet in python
Write to csv with Python
Convert SDF to CSV quickly
How to use Google Colaboratory