Using gspread, trying to add a column at the end of Google Sheet that already exists-CodePudding

Here is the code I am working with.

dfs=dfs[['Reserved']] #the column that I need to insert
dfs=dfs.applymap(str)    #json did not accept the nan so needed to convert
sh=gc.open_by_key('KEY')     #would open the google sheet 
sh_dfs=sh.get_worksheet(0)    #getting the worksheet
sh_dfs.insert_rows(dfs.values.tolist())    #inserts the dfs into the new worksheet

Running this code would insert the rows at the first column of the worksheet but what I am trying to accomplish is adding/inserting the column at the very last, column p.

CodePudding user response：

In your situation, how about the following modification? In this modification, at first, the maximum column is retrieved. And, the column number is converted to the column letter, and the values are put to the next column of the last column.

From:

sh_dfs.insert_rows(dfs.values.tolist())

To:

# Ref: https://stackoverflow.com/a/23862195
def colnum_string(n):
    string = ""
    while n > 0:
        n, remainder = divmod(n - 1, 26)
        string = chr(65   remainder)   string
    return string

values = sh_dfs.get_all_values()
col = colnum_string(max([len(r) for r in values])   1)
sh_dfs.update(col   '1', dfs.values.tolist(), value_input_option='USER_ENTERED')

Note:

If an error like exceeds grid limits occurs, please insert the blank column.

Reference:

update