Example of request body for creating a google spreadsheet in specific google drive location via python API?

Question

Trying to create a spreadsheet in a specific location on google drive using the python API, but the docs on how to do this leave out any concrete examples of what exactly the request body would look like.

Ultimately, have a pandas dataframe with a header and single row of values and trying to create a spreadsheet in google drive that looks like...

------------------
header1 | value1 |
------------------
header2 | value2 |
------------------
header3 | value3 |
------------------
header4 | value4 |
------------------
header5 | value5 |
------------------

where rows 1,2, and 5 are protected.

Some specific questions I have when looking at the docs for this kind of request's JSON body (that have stopped me so far from doing an initial test are) are...

1. What can I use as a spreadsheetId? What happens with naming conflicts? The docs don't seem to have any info / example on what this is or can be.
2. How would I know what spreadsheetUrl to use? Is there a default? What does this even mean / look like exactly?
3. Some subsections a the request body seem to want redundant info (eg. sheetId). Is this necessary / can I just leave blank?
4. How can I know / specify where in google drive this sheet is going to be created (I have a particular dir path where I want to create these spreadsheets)?

This is the request body that I have constructed so far (Note, here I'm just trying to get data into the spreadsheet in the conventional table format of row1=header, row2=values)...

df = <some dataframe>
HEADER = list(df.columns)
VALUES = df.values.tolist()[0]  # first row of values from dataframe

# building request body for API spreadsheets.create request
request_body = {
  "spreadsheetId": VALUES[0],  # this is a UUID for the data row in the pandas dataframe
  "properties": {
    "title": f"{DATANAME}__{VALUES[0]}",
  },
  "sheets": [
    {
        "properties": {
            "sheetId": 1,
            "title": DATANAME
        },
        "data": [
            {
              "startRow": 1,
              "startColumn": 1,
              "rowData": [
                {
                    "values": [
                        HEADER, # list if values
                        VALUES  # list of values
                    ]
                }
              ],
            }
        ],
        "protectedRanges": [
            {
              "protectedRangeId": integer, # what even is this?
              "range": {
                  {
                      "sheetId": 1, # do I really need to specify the sheetId again here?
                      "startRowIndex": 1,
                      "endRowIndex": 3,
                      "startColumnIndex": 1,
                      "endColumnIndex": 3
                  }
              },
              "description": "This is a metadata field and should not be edited",
              "warningOnly": False,
            },
            {
              "protectedRangeId": integer, # what even is this?
              "range": {
                  {
                      "sheetId": 1,
                      "startRowIndex": 5,
                      "endRowIndex": 6,
                      "startColumnIndex": 5,
                      "endColumnIndex": 6
                  }
              },
              "description": "This is a metadata field and should not be edited",
              "warningOnly": False,
            }
        ]
    }
  ],
  "spreadsheetUrl": string,
}

Could anyone with more experience here show me an example of what an actual working request body would look like in this case (and anything wrong with what I've constructed so far)? As well as answer some of the more specific question above?

Are there any other places to see more exmaples / info that the linked-on docs?

Answer 1

From responses to questions here and here, this is how I am currently creating a Spreadsheet in Drive and adding values and protected ranges to protect metadata values (via the python API client).

Basically, since the spreadsheets.create() method can't specify the Drive location, I have to use the Drive api to create the Spreadsheet in the specified location (by the Drive folder ID (the ID string you see in the Drive folder's URL)), get the assigned Spreadsheet ID from the request response, build a series of Sheets api requests, then send those requests via spreadsheets.batchUpdate (referrencing the created Spreadsheet's ID)

# getting data from pandas dataframe for writing
df = df.fillna("")
HEADER = [str(c) for c in df.columns]
VALUES = [str(v) for v in df.values.tolist()[0]]
data = [HEADER, VALUES]
print(HEADER, VALUES)

# using Drive API to create the Spreadsheet in specified Drive location
drive = build('drive', 'v3', credentials=creds)
file_metadata = {
    'name': 'sampleName',
    'parents': ['### folderId ###'],
    'mimeType': 'application/vnd.google-apps.spreadsheet',
}
res = drive.files().create(body=file_metadata).execute()
print(res)
SPREADSHEET_ID = res['id']

# building request object
requests = [
    {  
        "appendCells":
        {
            "sheetId": 0,
            "rows": [
                {   # need to build the individual cell value objects for the request
                    "values": [{"userEnteredValue": {"stringValue": v}} for v in HEADER],
                },
                {
                    "values": [{"userEnteredValue": {"stringValue": v}} for v in VALUES],
                }
            ],
            "fields": "*"
         },
    },
    {  
        "addProtectedRange": {
            # protecting header fields
            "protectedRange": {
                "range": {
                    "sheetId": 0,  # sheets are indexed from 0
                    "startRowIndex": 0,
                    "endRowIndex": 1,  # pretty sure the endIndexes are exclusive
                    "startColumnIndex": 0,
                    "endColumnIndex": len(HEADER)
                },
                "description": "Do not edit this header data",
                "warningOnly": False,
            }
        }
    },
    {
        "addProtectedRange": {
            # protecting metadata values
            "protectedRange": {
                "range": {
                    "sheetId": 0,  # sheets are indexed from 0
                    "startRowIndex": 0,
                    "endRowIndex": 2,  # pretty sure the endIndexes are exclusive
                    "startColumnIndex": 0,
                    "endColumnIndex": 2
                },
                "description": "This is a metadata field and should not be edited",
                "warningOnly": False,
            }
        }
    },
    {
        "addProtectedRange": {
            # protecting metadata values
            "protectedRange": {
                "range": {
                    "sheetId": 0,  # sheets are indexed from 0
                    "startRowIndex": 0,
                    "endRowIndex": 2,  # pretty sure the endIndexes are exclusive
                    "startColumnIndex": 8,
                    "endColumnIndex": 10
                },
                "description": "This is a metadata field and should not be edited",
                "warningOnly": False,
            }
        }
    }
]

# do the batch updates on the Spreadsheet
request_body = {"requests": requests}
assert "requests" in request_body.keys()
assert isinstance(request_body["requests"], typing.List)
sheets = discovery.build('sheets', 'v4', credentials=credentials)
request = sheets.spreadsheets() \
            .batchUpdate(spreadsheetId=SPREADSHEET_ID, body=request_body)
request.execute()
print(request)

References: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate#examples https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#appendcellsrequest https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#addprotectedrangerequest