CODEWITHSUNDEEP
pythongoogle-sheets
Emm
Emm

Reputation: 2507

Insert values into first empty row in Google Sheets

Trying to run a python function that will insert some values into the first empty row in the spreadsheet. I cannot figure out how to find the first empty row and insert data into it.

Here is a sample of my code:

scope = ["https://spreadsheets.google.com/feeds","https://www.googleapis.com/auth/spreadsheets","https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)
sheet = client.open('Payment Tracker').sheet1
data = sheet.get_all_records()
today = datetime.today().strftime('%m-%d-%Y')
insertRow = ['Person','1000',today,'Bank']
row = sheet.row_values(4)
sheet.insert_row(row, 2)

Upvotes: 0

Views: 1398

Answers (1)

Tanaike
Tanaike

Reputation: 201553

I believe your goal as follows.

  • You want to put the value of insertRow to the 1st empty row in the sheet using gspread with python.

For this, how about this answer?

Pattern 1:

In this pattern, append_row is used. In this case, the values are put to the last row of the sheet.

Modified script:

Please modify your script as follows.

From: 
row = sheet.row_values(4)
sheet.insert_row(row, 2)
To: 
sheet.append_row(insertRow, value_input_option='USER_ENTERED')

Pattern 2:

In this pattern, the values are put to the 1st empty row of the sheet. In this case, when the empty row is "A3" under the data range is "A1:A5", the value is put to the row 3.

Modified script:

Please modify your script as follows.

From: 
data = sheet.get_all_records()
today = datetime.today().strftime('%m-%d-%Y')
insertRow = ['Person','1000',today,'Bank']
row = sheet.row_values(4)
sheet.insert_row(row, 2)
To: 
data = sheet.get_all_values()
row = 0
for i, r in enumerate(data):
    if all([c == '' for c in r]) is True:
        row = i + 1
        break
today = datetime.today().strftime('%m-%d-%Y')
insertRow = ['Person', '1000', today, 'Bank']
spreadsheet.values_update('A' + str(row), params={'value_input_option': 'USER_ENTERED'}, body={'values': [insertRow]})

References:

Upvotes: 1

Related Questions