Here’s a Google Cloud Function that uses the Google Sheets API to add arbitrary json to a Google Sheet: https://github.com/krypted/tinyconverters/blob/main/json_to_GoogleSheet.py
To use the function, create a Google Cloud project and enable the Google Sheets API. Then create a service account and download the JSON key file for that account and place it in the same directory as the script. Once the Cloud Function is deployed, a request similar to the following will add the nested JSON data to the spreadsheet with the ID for that sheet swapped in with spreadsheetId in the below `curl`:
curl -X POST \
-H "Content-Type: application/json" \
-d '{
"spreadsheetId": "spreadsheetId",
"range": "range",
"json": {
"key1": "value1",
"key2": "value2"
}
}' \
https://us-central1-PROJECT_ID.cloudfunctions.net/addData
This allows us to pipeline information in a variety of ways, like a web hook that can accept events, etc.