0

I am creating a database that stores employee time. They put their time on a Google Spreadsheet, which is downloaded and put into a CSV. Then the CSV is put into a database.
The problem: if they leave a cell blank in the google sheet (specifically the final cell), the download stops.

My python script:

credentials = get_credentials()
http = credentials.authorize(httplib2.Http())
discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
                'version=v4')
service = discovery.build('sheets', 'v4', http=http,
                          discoveryServiceUrl=discoveryUrl)

spreadsheetId = '***************************'
rangeName = 'Master Time!A:E'
result = service.spreadsheets().values().get(
    spreadsheetId=spreadsheetId, range=rangeName).execute()
values = result.get('values', [])
#set up csv
if not values:
    print('No data found.')
else:
    with open('MasterTime_notFixed.csv', "w", newline='') as csvfile:
        writer=csv.writer(csvfile, delimiter=',')
        for row in values:
            writer.writerow(row)
            print('%s, %s, %s, %s, %s' % (row[0], row[1], row[2], row[3], row[4]))`

Is there a way to either automatically add value to the cells in google spreadsheet, or another option?
So far I have tried to add another row and fill with data, then not get that data, but it gives me way more than I need.

The end goal is this to be very hands off. So once it is being used, the script should not have to be touched.

Edits:

The spreadsheet:
Spreadsheet with blank spaces

The error:

Traceback (most recent call last):
Employee, Date, Job Number, Hours, Description
SomeEmpName, 2018-02-26, 80112-17, 8, field ck with john
  File "C:/Users/JP2-GIS2/Desktop/GIS/SW/Time/TimeSheet.py", line 102, in 
<module>
SomeEmpName, 2018-02-28, 80008-18, 8, stk cg with john
SomeEmpName, 2018-02-27, 40000, 1, dept meeting
    main()
  File "C:/Users/JP2-GIS2/Desktop/GIS/SW/Time/TimeSheet.py", line 100, in 
main
print('%s, %s, %s, %s, %s' % (row[0], row[1], row[2], row[3], row[4]))
IndexError: list index out of range

Process finished with exit code 1

The code stops at the row that has blank spaces.

tehhowch
  • 9,645
  • 4
  • 24
  • 42
Swagoner
  • 29
  • 6
  • When you say "the download stops", what do you mean? Can you share the error messages you get? Can you share the code you used to add values? Can you share an example result structure from `result.get('values', [])` (e.g. in a table)? – tehhowch Mar 01 '18 at 20:53
  • I updated my question with the spreadsheet and the error code. – Swagoner Mar 02 '18 at 15:23
  • The error you get (`List index out-of-range`) indicates that the only issue is that your `print` command is failing (the download was OK). A better title for your question is *"How to supply a default value for missing list index."* This question has already been asked: https://stackoverflow.com/questions/2574636/getting-a-default-value-on-index-out-of-range-in-python and https://stackoverflow.com/questions/2492087/how-to-get-the-nth-element-of-a-python-list-or-a-default-if-not-available **You could simply remove the print command and avoid needing to handle the error** – tehhowch Mar 02 '18 at 15:43
  • Well that is what I get for not thinking through the problem fully. Thanks for the help – Swagoner Mar 02 '18 at 16:04

0 Answers0