Gspread - Move column down as soon as new row is inserted

Question

I have a little script that gets data out of an API and writes it to a google spreadsheet. This works with gspread.

I want the script to run every night to check for new updates.

Currently it looks like this

Event date - Event Location - Performer - Comment

Problem is that the last row will be inserted manually and the first 3 rows will be data fed from a json file.

If I run the script on monday night and the dates are like following

15/10/1 - comment for the 1st
15/10/2 - comment for the 2nd
15/10/10 - comment for the 10th

and then on tuesday 15/10/5 gets added it will overwrite the cells correctly but the comments will stay on the same row it was manually added on. Like this:

15/10/1 - comment for the 1st
15/10/2 - comment for the 2nd
15/10/5 - comment for the 10th
15/10/10 - no comment

How do I make sure even though some new lines are inserted in between that the corresponding comment moves down as well.

The data just gets rewritten and not pushed down.

 def getActivity(query):

   data = query.get_data("/events/event%3Aprofile%3A%22demo(c)%22%20event%3Adate%3A%23next365days%20(event%3Alocation%3Aconcertzaal%20%2B%20event%3Alocation%3Atheaterzaal%20%2B%20event%3Alocation%3Abalzaal%20%2B%20event%3Alocation%3Acafe)")
   i=0

   eventdata = {}

   for w in data["data"]:
     id=data["data"][i]["id"]
     location = data['data'][i]['locations'][0]['name']
     start = data['data'][i]['starttime']
     artiest = data['data'][i]['name']
     status = data['data'][i]['status']['name']

     eventdata[start] = [lokatie, artiest, status]

     i=i+1

   return eventdata


 def exportToSheet(query, worksheet, eventdata):

   # worksheet.update_cell(5, 5, naam)
   cellOffset = 2

   for key in sorted(eventdata):

     start = key
     location = eventdata[key][0]
     artiest = eventdata[key][1]
     status = eventdata[key][2]




     if location == "CONCERTZAAL":
       worksheet.update_cell(cellOffset, 1, start)
       worksheet.update_cell(cellOffset, 3, artiest)
       worksheet.update_cell(cellOffset, 4, status)
       cellOffset=cellOffset+1

     if location == "BALZAAL":
       worksheet.update_cell(cellOffset, 5, start)
       worksheet.update_cell(cellOffset, 7, artiest)
       worksheet.update_cell(cellOffset, 8, status)
       cellOffset=cellOffset+1

     if location == "THEATERZAAL":
       worksheet.update_cell(cellOffset, 9, start)
       worksheet.update_cell(cellOffset, 11, artiest)
       worksheet.update_cell(cellOffset, 12, status)
       cellOffset=cellOffset+1




   worksheet.update_cell(1,1, "CONCERTZAAL")
   worksheet.update_cell(1,2, "Opmerking")
   worksheet.update_cell(1,3, "Artist")
   worksheet.update_cell(1,4, "Status")

   worksheet.update_cell(1,5, "BALZAAL")
   worksheet.update_cell(1,6, "Opmerking")
   worksheet.update_cell(1,7, "Artist")
   worksheet.update_cell(1,8, "Status")

   worksheet.update_cell(1,9, "THEATERZAAL")
   worksheet.update_cell(1,10, "Opmerking")
   worksheet.update_cell(1,11, "Artist")
   worksheet.update_cell(1,12, "Status")

This is what the excel looks like if it helps: https://i.gyazo.com/85a3b2de5a5fd3e2b1d0c7877b5d5921.png

score 0 · Answer 1 · answered Aug 21 '15 at 13:24

My workaround is following:

I save the custom fields together with the related ID of the event.

Afterwards I clear the whole thing, feed in the new data.

Then I loop over the saved comment/id and search for fields that contain the old saved ID and paste the comment in the correct cell.

Quite a bit of code but this works!

Gspread - Move column down as soon as new row is inserted

1 Answers1