0

I have a list of urls that I'm trying to upload to a spreadsheet with pygsheets. For some reason, instead of updating cells in the specified range, the script only updates one cell. Relevant part of the script below:

wks = sheet.sheet1
wks.update_cells('A1:A5',[[img]])

where img is a list of URLs. Any help would be greatly appreciated!

updated snipped of code

res_list = []
wks = sheet.sheet1
header = wks.cell('A1')
header.value = 'URLs'
for i in res_list:
   wks.update_values(crange='A2:A1000',
   values=np.array([[image_name]]).tolist(), majordim='ROWS')
Nithin
  • 5,470
  • 37
  • 44
Matthew
  • 411
  • 6
  • 22

1 Answers1

1

the update_cells take a matrix (list of lists). so remove the extra outer list. This should work.

now as you are writing each value in each column, you should convert your values into column major form

matrix = list(map(list, zip(*[img])))
wks = sheet.sheet1
wks.update_cells('A1:A5',[img])

else change major dimension

wks = sheet.sheet1
wks.update_cells('A1:A5',[img], majordim="COLUMNS")

EDIT: updated code 

res_list = []
wks = sheet.sheet1
header = wks.cell('A1')
header.value = 'URLs'
j=2
for i in res_list:
   image_name = get_new_name() # get the new url/value for next cell
   wks.update_values(crange="A"+str(j), values=np.array([[image_name]]).tolist())
   j+=1

EDIT2: working version (switched to earlier version of pygsheets)

    res_list = []
    wks = sheet.sheet1
    h = res_list
    im_name = [str(i) for i in h]
    for i in im_name:
        wks.update_cell('A'+str(im_name.index(i)+1), i)
Matthew
  • 411
  • 6
  • 22
Nithin
  • 5,470
  • 37
  • 44
  • Unfortunately the output still goes only to one cell. Forgot to mention, that those urls are in fact individual strings, which I'm trying to upload one after another. – Matthew Nov 12 '18 at 16:36
  • please show an example of img, also what is the version of your pygsheets? – Nithin Nov 12 '18 at 17:59
  • example of img: photo_1234.jpg. I am running the most recent version of pygsheets. – Matthew Nov 12 '18 at 19:22
  • you should make the img's into a list and then use the code provided. – Nithin Nov 12 '18 at 20:45
  • if you want to upload one after another loop and call update_cells – Nithin Nov 12 '18 at 20:46
  • will do! Thank you for your help! – Matthew Nov 12 '18 at 22:02
  • I've done everything you have recommended and I can see the output still being iterated over in a single cell - the urls are being replaced each time there is a new one instead of being added to a new cell. – Matthew Nov 12 '18 at 23:59
  • Please show your complete code. Or a complete code to reproduce the issue. btw as you are looping are you changing the adress 'A1:A5' accordingly ? – Nithin Nov 13 '18 at 09:34
  • nyway if you are looping and changing cells one by one, you can use wks.update_cell((i,1), img), where i is row number – Nithin Nov 13 '18 at 09:36
  • Thank you! now it populates multiple cells. It still prints out the same link in all of them, but I should be able to figure out how to change that. One last question. Is get_new_name() part of the pygsheet documentation? – Matthew Nov 13 '18 at 16:43
  • no, i just used a dummy function get_new_name, because in your code you didnt show how you are getting the next link. you should basically change that line with your logic of getting next value for next cell – Nithin Nov 13 '18 at 17:46
  • just added the final version of that part of the script. Turned out downgrading pygsheets and using update_cell method worked for me – Matthew Nov 14 '18 at 16:00