CODEWITHSUNDEEP
pythongoogle-drive-apigoogle-sheets-api
user14398375
user14398375

Reputation: 175

spreadsheets api how to specify the range when there are columns with data

i need to append data to a spreadsheet that have defined data only in some columns so i have to complete the information in the rest cells, when i append the new data it is writed at the end of the sheet where there all the cells are blanks

here is my code to append without the auth stuff

service = build('sheets', 'v4', credentials=creds)

# Call the Sheets API
sheet = service.spreadsheets()

data=[["data","data","data","data","https://google.com"]]
res = sheet.values().append(spreadsheetId="134542fvbvfdfewdvwed4",
                            range="sheet!A1:G1",
                            valueInputOption="USER_ENTERED",
                            insertDataOption="INSERT_ROWS",
                            body={"values":data}).execute()
print(res)

how the sheet looks like enter image description here

Upvotes: 0

Views: 769

Answers (1)

JaHoff
JaHoff

Reputation: 206

In order to write in a specified range you will want to use the update function instead of append, and add the vertical range in the second column declaration (G523):

sheet.values().update(spreadsheetId="134542fvbvfdfewdvwed4",
                        range="sheet!A1:G523",
                        valueInputOption="USER_ENTERED",
                        body={"values":data}).execute()

Appending to the right of the formula cells is easiest through a second call to sheet.values().update:

sheet.values().update(spreadsheetId="134542fvbvfdfewdvwed4",
                        range="sheet!H1:I523",
                        valueInputOption="USER_ENTERED",
                        body={"values":data_part2}).execute()

As far as I know you cannot skip cells or columns in a single update.

Upvotes: 1

Related Questions