I can't properly adjust the google spreadsheet body using python-CodePudding

I am able to make a new blank google spreadsheet using python. Everything works fine except the fact that i can't figure out how to properly adjust the spreadsheet body to my needs. What i really wanna do is to pass values in the cells of the first row (kinda like headers), have those values centered in each cell and finally have those cells colored green. I know this is possible to do when creating a new spreadsheet(having read google sheets for developers multiple times),but i just can't figure out how to do it. Any help or tip would be much appreciated.

import os.path
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from pprint import pprint
from googleapiclient import discovery
import gspread
from google.oauth2.credentials import Credentials
from google.oauth2 import service_account
from googleapiclient.discovery import build
import sys

SCOPES = ["https://www.googleapis.com/auth/drive.file"]


creds = None
    # The file token.json stores the user's access and refresh tokens, and is
    # created automatically when the authorization flow completes for the first
    # time.
if os.path.exists('token.json'):
   creds = Credentials.from_authorized_user_file('token.json', SCOPES)
    # If there are no (valid) credentials available, let the user log in.
if not creds or not creds.valid:
   if creds and creds.expired and creds.refresh_token:
      creds.refresh(Request())
   else:
      flow = InstalledAppFlow.from_client_secrets_file(
         'desktop_client.json', SCOPES)
   #creds = flow.run_local_server(port=0)
        # Save the credentials for the next run
   with open('token.json', 'w') as token:
      token.write(creds.to_json())
service = build('sheets', 'v4', credentials=creds)

#sheet_name = str(sys.argv[1])
folder_id = str(sys.argv[2])

spreadsheet_body = {
'properties':{
   #spreadsheet name
   'title':'test'
},
 'sheets':[{
   'properties':{
      #worksheet name
      'title':'Φύλλο1',
      'gridProperties':{
         #row\column number
         'rowCount':100,
         'columnCount':11
      },
   },
 }
 ]
}


request = service.spreadsheets().create(body=spreadsheet_body)
response = request.execute()

# TODO: Change code below to process the `response` dict:
#pprint(response['spreadsheetUrl'])
pprint(response)

# Move the created Spreadsheet to the specific folder.
drive = build('drive', 'v3', credentials=creds)

folderId = folder_id

res = drive.files().update(fileId=response['spreadsheetId'], addParents=folderId, removeParents='root').execute()
#print(res)

CodePudding user response：

I believe your goal is as follows.

When you create a new Google Spreadsheet, you want to include the header row in the created Spreadsheet.
At the header row, each text is aligned to the center of the cell and the background color of cells is green.
You want to achieve this using googleapis for python.

In this case, how about the following modification?

From:

spreadsheet_body = {
'properties':{
   #spreadsheet name
   'title':'test'
},
 'sheets':[{
   'properties':{
      #worksheet name
      'title':'Φύλλο1',
      'gridProperties':{
         #row\column number
         'rowCount':100,
         'columnCount':11
      },
   },
 }
 ]
}

To:

header_values = ['sample1', 'sample2', 'sample3'] # Please set the header values.
header_row = [{'userEnteredValue': {'stringValue': e}, 'userEnteredFormat': {'horizontalAlignment': 'CENTER', 'backgroundColorStyle': {
    'rgbColor': {
        'red': 0,
        'green': 1,
        'blue': 0
    }}}}
    for e in header_values]

spreadsheet_body = {
    'properties': {
        # spreadsheet name
        'title': 'test'
    },
    'sheets': [{
        'properties': {
            # worksheet name
            'title': 'Φύλλο1',
            'gridProperties': {
                # row\column number
                'rowCount': 100,
                'columnCount': 11
            },
        },
        'data': [{'rowData': [{'values': header_row}]}] # Added
    }
    ]
}

Note:

This modified script supposes that you have already been able to create a new Spreadsheet using Sheets API. Please be careful about this.

Reference:

Method: spreadsheets.create