CODEWITHSUNDEEP
pythongoogle-sheetspygsheets
Pechi
Pechi

Reputation: 186

How to identify the first empty column in a Google Sheet to append a dataframe using Python?

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.

Upvotes: 1

Views: 922

Answers (1)

Tanaike
Tanaike

Reputation: 201553

  • 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.

Upvotes: 2

Related Questions