Get Response from Existing Google Spreadsheet, Append Rows and Update

Question

I can connect to a Google Spreadsheet and add new rows (actual values from a list) as shown below and it works:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

from pprint import pprint
from googleapiclient import discovery

scope = ['https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive',
    'https://www.googleapis.com/auth/spreadsheets.currentonly',
    'https://www.googleapis.com/auth/spreadsheets']
credentials = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
gc = gspread.authorize(credentials)

service = discovery.build('sheets', 'v4', credentials=credentials)

#Name of Spreadsheet
Sheet_Title = "New Spreadsheet Final"

# The ID of the spreadsheet 
spreadsheet_id = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'  # TODO: Update placeholder value.
#Get sheet ID

#Open the wanted Tab
sheet = gc.open(Sheet_Title).worksheet("Tab One")  

#Get the wanted range of the sheet
range_name = 'A1:W1000'  # TODO: Update placeholder value.

#Add new rows in the Sheet. Values are hardcoded below
values = [
    ['09/01/2021', 'IT', '8%'],
    ['08/02/2021', 'NL', '1%']
    # Additional rows ...
]
body = {
    'values': values
}

#Append the rows in the Google Spreadsheet
result = service.spreadsheets().values().append(
    spreadsheetId=spreadsheet_id, range=range_name,
    valueInputOption='USER_ENTERED', body=body).execute()

and what I am trying to do now is to add new rows but for each cells (or generally specific columns) add a data validation. So, I went down and checked the response. I then appended a new row value that includes the format and the data validation:

#Values to be added in the original query
value_to_be_added = [
              {
                "formattedValue": "05/05/2019",
                "userEnteredFormat": {
                  "numberFormat": {
                    "type": "DATE",
                    "pattern": "dd/mm/yyyy"
                  }
                },
                "dataValidation": {
                  "condition": {
                    "type": "DATE_IS_VALID"
                  },
                  "strict": "True"
                }
              },
              {
                "formattedValue": "DE",
                "userEnteredFormat": {
                  "numberFormat": {
                    "type": "NUMBER",
                    "pattern": "#,##0.00"
                  },
                  "verticalAlignment": "BOTTOM"
                },
                "dataValidation": {
                  "condition": {
                    "type": "ONE_OF_LIST",
                    "values": [
                      {
                        "userEnteredValue": "UK"
                      },
                      {
                        "userEnteredValue": "ES"
                      },
                      {
                        "userEnteredValue": "IT"
                      },
                      {
                        "userEnteredValue": "DE"
                      },
                      {
                        "userEnteredValue": "AT"
                      },
                      {
                        "userEnteredValue": "NL"
                      }
                    ]
                  },
                  "showCustomUi": "True"
                }
              },
              {
                "formattedValue": "4%",
                "userEnteredFormat": {
                  "numberFormat": {
                    "type": "PERCENT",
                    "pattern": "0%"
                  }
                }
              }
            ]

In other words, I have tried manipulating the original response JSON and the only thing I need is to update the full file but I cannot find the correct update function:

#Get request to get the full Google Spreadsheet in a JSON
request = service.spreadsheets().get(spreadsheetId=spreadsheet_id, ranges=range_name, includeGridData=True)
response = request.execute()

#dictionary that will be appended in the values of the response
mydict = {}

#Assign dictionary with the new value. We have one value in this case.
mydict[row_count+1] = value_to_be_added

#print(mydict[row_count+1])

#Add new row in the response.
response['sheets'][0]['data'][0]['rowData'][x]['values'].append(mydict[row_count+1])

#updated response that has to be sent/updated in the API.
new_response = response

Is there a way to fully update/replace the original response with the updated one?

The original Spreadsheet:

The final Spreadsheet after my update.request (how I want it to be):

where you can see one new row with the data validation applying (it can be an X amount of rows).

In order to correctly understand your situation, can you provide a sample Spreadsheet including the input and output you want? By this, I would like to think of the solution. Of course, please remove your personal information. — Tanaike, Sep 26 '19 at 22:08
Thank you for replying and adding the information. From your additional information, I proposed a sample script as an answer. Could you please confirm it? If I misunderstood your question and that was not the result you want, I apologize. — Tanaike, Sep 27 '19 at 11:57

Tanaike · Accepted Answer · 2019-09-27T21:44:19.533

You want to append a row to the next row of the last row of Spreadsheet.
- You want to append a row like the date value of dd/mm/yyyy, the data validation, the number of 0% format and the number.
- You want to put the values of 05/05/2019, UK, 8%, 9 as the display values.
You want to achieve this using gspread with python.
You have already been able to get and put values for Spreadsheet using Sheets API.

If my understanding is correct, how about this sample script? In this sample script, I used the method of batch_update of gspread. The method of batch_upate of gspread is the method of spreadsheets.batchUpdate of Sheets API. And the row is appended by the appendCells request.

Sample script:

The sample script is as follows. Before you run the script, please set the Spreadsheet ID and sheet name. In this script, Tab One is used as the sheet name.

gc = gspread.authorize(credentials)
spreadsheet_id = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
sheetName = "Tab One"
spreadsheet = gc.open_by_key(spreadsheet_id)
sheetId = spreadsheet.worksheet(sheetName)._properties['sheetId']
body = {'requests':
        [
            {
                'appendCells':
                {
                    'rows':
                    [
                        {
                            'values':
                            [
                                {
                                    'userEnteredFormat':
                                    {
                                        'numberFormat':
                                        {
                                            'type': 'DATE',
                                            'pattern': 'dd/mm/yyyy'
                                        }
                                    },
                                    'dataValidation':
                                    {
                                        'condition':
                                        {
                                            'type': 'DATE_IS_VALID'
                                        },
                                        'strict': True
                                    },
                                    'userEnteredValue':
                                    {
                                        'numberValue': 43590  # This is the serial number of "05/05/2019".
                                    }
                                },
                                {
                                    'userEnteredFormat':
                                    {
                                        'numberFormat':
                                        {
                                            'type': 'NUMBER',
                                            'pattern': '#,##0.00'
                                        },
                                        'verticalAlignment': 'BOTTOM'
                                    },
                                    'dataValidation':
                                    {
                                        'condition':
                                        {
                                            'type': 'ONE_OF_LIST',
                                            'values':
                                            [
                                                {
                                                    'userEnteredValue': 'UK'
                                                },
                                                {
                                                    'userEnteredValue': 'ES'
                                                },
                                                {
                                                    'userEnteredValue': 'IT'
                                                },
                                                {
                                                    'userEnteredValue': 'DE'
                                                },
                                                {
                                                    'userEnteredValue': 'AT'
                                                },
                                                {
                                                    'userEnteredValue': 'NL'
                                                }
                                            ]
                                        },
                                        'showCustomUi': True
                                    },
                                    'userEnteredValue':
                                    {
                                        'stringValue': 'UK'
                                    }
                                },
                                {
                                    'userEnteredFormat':
                                    {
                                        'numberFormat':
                                        {
                                            'type': 'PERCENT',
                                            'pattern': '0%'
                                        }
                                    },
                                    'userEnteredValue':
                                    {
                                        'numberValue': 0.08
                                    }
                                },
                                {
                                    'userEnteredValue':
                                    {
                                        'numberValue': 9
                                    }
                                }
                            ]
                        }
                    ],
                    'sheetId': sheetId,
                    'fields': '*'
                }
            }
        ]
        }
res = spreadsheet.batch_update(body)
print(res)

Note:

When 05/05/2019 is put as the date of the format of dd/mm/yyyy, please put the number of 43590 which is the serial number.

References:

Added:

When you want to use the request body in the for loop, how about the following script? The batchUpdate method can be used with an array.

gc = gspread.authorize(credentials)
spreadsheet_id = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
sheetName = "Tab One"
spreadsheet = gc.open_by_key(spreadsheet_id)
sheetId = spreadsheet.worksheet(sheetName)._properties['sheetId']
requests = []
for x in range(len(new_values)):  # Please set "new_values".
    body =                 {
                    'appendCells':
                    {
                        'rows':
                        [
                            {
                                'values':
                                [
                                    {
                                        'userEnteredFormat':
                                        {
                                            'numberFormat':
                                            {
                                                'type': 'DATE',
                                                'pattern': 'dd/mm/yyyy'
                                            }
                                        },
                                        'dataValidation':
                                        {
                                            'condition':
                                            {
                                                'type': 'DATE_IS_VALID'
                                            },
                                            'strict': True
                                        },
                                        'userEnteredValue':
                                        {
                                            'numberValue': new_values[x][0]
                                        }
                                    },
                                    {
                                        'userEnteredFormat':
                                        {
                                            'numberFormat':
                                            {
                                                'type': 'NUMBER',
                                                'pattern': '#,##0.00'
                                            },
                                            'verticalAlignment': 'BOTTOM'
                                        },
                                        'dataValidation':
                                        {
                                            'condition':
                                            {
                                                'type': 'ONE_OF_LIST',
                                                'values':
                                                [
                                                    {
                                                        'userEnteredValue': 'UK'
                                                    },
                                                    {
                                                        'userEnteredValue': 'ES'
                                                    },
                                                    {
                                                        'userEnteredValue': 'IT'
                                                    },
                                                    {
                                                        'userEnteredValue': 'DE'
                                                    },
                                                    {
                                                        'userEnteredValue': 'AT'
                                                    },
                                                    {
                                                        'userEnteredValue': 'NL'
                                                    }
                                                ]
                                            },
                                            'showCustomUi': True
                                        },
                                        'userEnteredValue':
                                        {
                                            'stringValue': new_values[x][1]
                                        }
                                    },
                                    {
                                        'userEnteredFormat':
                                        {
                                            'numberFormat':
                                            {
                                                'type': 'PERCENT',
                                                'pattern': '0%'
                                            }
                                        },
                                        'userEnteredValue':
                                        {
                                            'numberValue': new_values[x][2]
                                        }
                                    },
                                    {
                                        'userEnteredValue':
                                        {
                                            'numberValue': new_values[x][2]
                                        }
                                    }
                                ]
                            }
                        ],
                        'sheetId': sheetId,
                        'fields': '*'
                    }
                }
    requests.append(body)

res = spreadsheet.batch_update({'requests':requests})
print(res)

That works and I am going to accept the answer. I am also going to post my version based on yours. There might be an easier way you can suggest. — Datacrawler, Sep 27 '19 at 15:31
@Apolo Radomer Thank you for replying. I'm glad your issue was resolved. I added one more sample script for your additional script. Could you please confirm it? — Tanaike, Sep 27 '19 at 21:44
Yes. I can see you added that part. In my original script I have also converted the date to a 5-digit serial code and added everything into functions (although for a reason that doesn't make the script faster but cleaner). — Datacrawler, Sep 28 '19 at 10:41

score 0 · Answer 2 · answered Sep 27 '19 at 15:33

My version based on Tanaike's answer:

#List of rows with values
new_values = [[43590,'UK',0.08,9],[43590,'DE',0.07,8]]

#connect to the google sheet and tab
sheetName = "Tab One"
spreadsheet = gc.open_by_key(spreadsheet_id)
sheetId = spreadsheet.worksheet(sheetName)._properties['sheetId']

#Add each value/row
for x in range(len(new_values)):
    #print(new_values[x])
    body = {'requests':
            [
                {
                    'appendCells':
                    {
                        'rows':
                        [
                            {
                                'values':
                                [
                                    {
                                        'userEnteredFormat':
                                        {
                                            'numberFormat':
                                            {
                                                'type': 'DATE',
                                                'pattern': 'dd/mm/yyyy'
                                            }
                                        },
                                        'dataValidation':
                                        {
                                            'condition':
                                            {
                                                'type': 'DATE_IS_VALID'
                                            },
                                            'strict': True
                                        },
                                        'userEnteredValue':
                                        {
                                            'numberValue': new_values[x][0]  # This is the serial number of "05/05/2019".
                                        }
                                    },
                                    {
                                        'userEnteredFormat':
                                        {
                                            'numberFormat':
                                            {
                                                'type': 'NUMBER',
                                                'pattern': '#,##0.00'
                                            },
                                            'verticalAlignment': 'BOTTOM'
                                        },
                                        'dataValidation':
                                        {
                                            'condition':
                                            {
                                                'type': 'ONE_OF_LIST',
                                                'values':
                                                [
                                                    {
                                                        'userEnteredValue': 'UK'
                                                    },
                                                    {
                                                        'userEnteredValue': 'ES'
                                                    },
                                                    {
                                                        'userEnteredValue': 'IT'
                                                    },
                                                    {
                                                        'userEnteredValue': 'DE'
                                                    },
                                                    {
                                                        'userEnteredValue': 'AT'
                                                    },
                                                    {
                                                        'userEnteredValue': 'NL'
                                                    }
                                                ]
                                            },
                                            'showCustomUi': True
                                        },
                                        'userEnteredValue':
                                        {
                                            'stringValue': new_values[x][1]
                                        }
                                    },
                                    {
                                        'userEnteredFormat':
                                        {
                                            'numberFormat':
                                            {
                                                'type': 'PERCENT',
                                                'pattern': '0%'
                                            }
                                        },
                                        'userEnteredValue':
                                        {
                                            'numberValue': new_values[x][2]
                                        }
                                    },
                                    {
                                        'userEnteredValue':
                                        {
                                            'numberValue': new_values[x][3]
                                        }
                                    }
                                ]
                            }

                        ],
                        'sheetId': sheetId,
                        'fields': '*'
                    }
                }
            ]
            }
    res = spreadsheet.batch_update(body)

where the body gets pulled in every loop. You comment any alternatives if you think this is not the best way (looping the body). It might be easier to have a loop and append the values array in the body object.

Regarding [your deleted question](https://stackoverflow.com/q/60545418/3832970), you could probably make use of [a regex trie](https://stackoverflow.com/a/42789508/3832970). — Wiktor Stribiżew, Mar 05 '20 at 18:28

Get Response from Existing Google Spreadsheet, Append Rows and Update

2 Answers2

Sample script:

Note:

References:

Added: