1

I want to append a dataframe to a Google sheet that already has some values. I want to identify the first empty column and append the dataframe there.

Currently I'm pasting a dataframe to Google sheet using the code:

import pygsheets 

gc = pygsheets.authorize(service_file='client_secret.json')
sh = gc.open_by_url('URL') 

wks = sh[0] #Selecting the sheet 
wks.set_dataframe(df, 'A1')

# A1 is the cell where the dataframe is pasted

I want to be able to identify this column 'A1' - where A is the first empty column in the Google sheet and paste my dataframe there.

How do I do this using Python? I'm currently using pygsheets, though I'm open to solutions using any other packages.

Mntfr
  • 483
  • 6
  • 20
Pechi
  • 151
  • 1
  • 6
  • Did my answer show you the result what you want? Would you please tell me about it? That is also useful for me to study. If this works, other people who have the same issue with you can also base your question as a question which can be solved. If you have issues for my answer yet, I apologize. At that time, can I ask you about your current situation? I would like to study to solve your issues. – Tanaike Jun 18 '19 at 06:20
  • Hi. Thank you! Your answer was helpful. However, my final solution was a bit different. I'll post it here in a while. I'm unable to post the code properly in comments. – Pechi Jun 18 '19 at 06:24
  • Thank you for replying. I deeply apologize that my answer was not the direct solution. – Tanaike Jun 18 '19 at 06:49

1 Answers1

2
  • You want to put the values from the first empty column.
  • For example, the columns "A", "B" and "C" have values, you want to put new values from the column "D1".
  • You want to achieve this using Python and pygsheets.

If my understanding is correct, how about this modification? The flow of this modification is as follows.

  1. Existing values are retrieved using get_all_values().
  2. Retrieve the first empty column number.
  3. Convert the column number to the column letter.
  4. Put the additional value to the retrieved column.

Modified script:

Please modify your script in your question as follows.

From: 
wks.set_dataframe(df, 'A1')
To: 
import numpy as np  # Added

v = wks.get_all_values()
ar = np.array(v).transpose()
for i, e in enumerate(ar):
    r = any(x for x in e)
    if r is False:

        # https://stackoverflow.com/a/23862195/7108653
        n = i + 1
        string = ''
        while n > 0:
            n, remainder = divmod(n - 1, 26)
            string = chr(65 + remainder) + string

        wks.set_dataframe(df, string + "1")
        break

Note:

  • I used "numpy" for above modified script.

If I misunderstood your question and this was not the result you want, I apologize.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • @Pechi Thank you for replying. If my answer was not useful for your situation, I apologize. – Tanaike Jun 13 '19 at 06:34
  • Could you help to re write this to add new data in first empty row and not column please? I have some hard time understanding this piece of code. – mama dede Jul 05 '19 at 03:13
  • @mama dede Can you post your issue as new question by including the detail information? By posting it, users including me can think of the solution. – Tanaike Jul 05 '19 at 03:14