CODEWITHSUNDEEP
pythongoogle-sheets-apipygsheets
Ravish Jha
Ravish Jha

Reputation: 445

How to update the bottom row(first empty row) of sheet with data using pygsheets?

I have a spreadsheet linked with the python module with 3 columns. I have tried to insert the new row of data using insert_rows function but it does not do anything and also does not throw any error, making it harder for me to narrow down the problem.

Here is what I have tried:

gc = pygsheets.authorize(service_file='File.json')

sh = gc.open('TwitterStream')

wks = sh[0]

t = Twython('ckey ','c_secret ')

results = t.search(q='tuberculosis', count=5, lang='en', result_type='popular',tweet_mode='extended')
all_tweets = results['statuses']

for tweet in all_tweets:
        tweetString = tweet["full_text"]
        userMentionList = tweet["entities"]["user_mentions"]
        if len(userMentionList)>0:
            for eachUserMention in userMentionList:
                name = eachUserMention["screen_name"]
                time = tweet["created_at"]
//This is the function
                wks.insert_rows(wks.rows, number=1,values=[tweetString, name, time], inherit=True)

Upvotes: 1

Views: 2362

Answers (1)

Tanaike
Tanaike

Reputation: 201693

I believe your goal and situation as follows.

  • You want to append the values to the next row of the last row onthe Spreadsheet using pygsheets with python.
  • You have already been able to get and put values to Google Spreadsheet using Sheets API.

For this, how about this answer? In this answer, I would like to propose to use the method of append_table. For this, at first, the values for putting to the sheet are created, and then, the created values are put to the sheet using the method of append_table.

Modified script:

When your script is modified, it becomes as follows.

From: 
for tweet in all_tweets:
        tweetString = tweet["full_text"]
        userMentionList = tweet["entities"]["user_mentions"]
        if len(userMentionList)>0:
            for eachUserMention in userMentionList:
                name = eachUserMention["screen_name"]
                time = tweet["created_at"]
                wks.insert_rows(wks.rows, number=1,values=[tweetString, name, time], inherit=True)
To: 
values = []  # Added
for tweet in all_tweets:
    tweetString = tweet["full_text"]
    userMentionList = tweet["entities"]["user_mentions"]
    if len(userMentionList) > 0:
        for eachUserMention in userMentionList:
            name = eachUserMention["screen_name"]
            time = tweet["created_at"]
            values.append([tweetString, name, time])  # Added

wks.append_table(values, start='A1', end=None, dimension='ROWS', overwrite=True)  # Added
  • In this modification, values is appended to the next row of the last row of the sheet wks.
  • In the specification of pygsheets, it seems that when overwrite=True is modified to overwrite=False, the new rows are added and put to the values.

Reference:

Upvotes: 3

Related Questions