CODEWITHSUNDEEP
pythongoogle-sheets
MistaOmega
MistaOmega

Reputation: 30

Adding a Table of values to a google sheet (python)

#Write to sheet
subjects = ['Maths', 'Physics', 'Geography', 'Biology',
            'Chemistry', 'ICT', 'Travel and Tourism', 'Computer Science',
            'History', 'LINC', 'Design and Technology', 'Art',
            'English Literature', 'Welsh', 'MFL', 'Music',
            'Business','Applied Science', 'Medical Science', 'Media']
values =[]
import random
random.shuffle(subjects)

chunks = [subjects[x:x+5] for x in range(0, len(subjects), 5)]
I = chunks[0]
c = 0
for element in I:
    temp = str(element)
    print(temp)
    values.append(temp)
    print(values)
body = {
    'values': values,
    'majorDimension':'COLUMNS'

}
result = service.spreadsheets().values().update(
    spreadsheetId=spreadsheetId, range="Data!A1:D5",
    valueInputOption='USER_ENTERED',body=body).execute()

Running this it will basically move some list elements about randomly, then split them into smaller lists so I can add them column by column, my range is currently A1:D5 on the google sheet however I have tried using just A1 and a single list element and I get the same error, for example only having "Geography" appended to the list

My error is as follows:

googleapiclient.errors.HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/1DIP0UqEuS8mcEgAiKnFXaSsyqiUq2LXkGa7VmAJS4S8/values/Data%21A1?valueInputOption=USER_ENTERED&alt=json returned "Invalid value at 'data.values[0]' (type.googleapis.com/google.protobuf.ListValue), "English Literature"">

Upvotes: 1

Views: 971

Answers (2)

MistaOmega
MistaOmega

Reputation: 30

subjects = ['Maths', 'Physics', 'Geography', 'Biology',
            'Chemistry', 'ICT', 'Travel and Tourism', 'Computer Science',
            'History', 'LINC', 'Design and Technology', 'Art',
            'English Literature', 'Welsh', 'MFL', 'Music',
            'Business','Applied Science', 'Medical Science', 'Media']
t = ['Maths','Chemistry']
E = 0
Alphabet = ['A','B','C','D','E']
tempList = []
import random
random.shuffle(subjects)

chunks = [subjects[x:x+5] for x in range(0, len(subjects), 5)]
I = chunks[0]
c = 0
#print(chunks[0])
for chunk in chunks:
    R = Alphabet[E]
    print(chunk)
    c = c+1
    values = [[chunk[0]],[chunk[1]],[chunk[2]],[chunk[3]],[chunk[4]],]
    body = {
    'values': values,
    'majorDimension':'ROWS'

    }
    result = service.spreadsheets().values().update(
        spreadsheetId=spreadsheetId, range="Data!"+R+"2:Z",
        valueInputOption='RAW',body=body).execute()
    E = E+1

Upvotes: 0

Tanaike
Tanaike

Reputation: 201553

It seems that values is 1 dimensional list. At spreadsheets().values().update(), the list has to be 2 dimensional list. So how about the following modification?

Pattern 1:

If you want to import the 5 values to "A1:A5", you can use this.

From :

body = {
    'values': values,
    'majorDimension':'COLUMNS'
}

To :

body = {
    'values': [values], # Modified
    'majorDimension':'COLUMNS'
}

Pattern 2:

If you want to import the 5 values to "A1:E1", you can use this.

From :

for element in I:
    temp = str(element)
    print(temp)
    values.append(temp)
    print(values)
body = {
    'values': values,
    'majorDimension':'COLUMNS'
}
result = service.spreadsheets().values().update(
    spreadsheetId=spreadsheetId, range="Data!A1:D5",
    valueInputOption='USER_ENTERED',body=body).execute()

To :

for element in I:
    temp = str(element)
    print(temp)
    values.append([temp]) # Modified
    print(values)
body = {
    'values': values,
    'majorDimension':'COLUMNS'
}
result = service.spreadsheets().values().update(
    spreadsheetId=spreadsheetId, range="Data!A1:E1", # Modified
    valueInputOption='USER_ENTERED',body=body).execute()

If I misunderstand your question, I'm sorry.

Upvotes: 2

Related Questions