0

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.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
himnaejah
  • 39
  • 1
  • 8

1 Answers1

0

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • This worked! Thank you so much! I've been searching it but could not find what you've provided. – himnaejah Jan 28 '22 at 02:00
  • I've never across this method previously and find it interesting! One additional comment. Apparently, the rows start from row 0 but is it possible to add the header into this? I'd like to put the original header as 'reserved'. In other words, the rows would start from the row 1. – himnaejah Jan 28 '22 at 02:14
  • I've tried the code gain, and changing the "1" to "2" make it to start from the Row B but not sure how to put the title in the header. I can probably find a workaround but it would be great to know if this function has the functionality to include the title. – himnaejah Jan 28 '22 at 02:36
  • @himnaejah Thank you for your response. About `One additional comment. Apparently, the rows start from row 0 but is it possible to add the header into this?`, in that case, how about modifying `col + '1'` to `col + '2'`? By this, the values are put from the row 2. Is this modification your expected result? If I misunderstood it, I apologize. – Tanaike Jan 28 '22 at 04:24
  • Yeah, that's exactly what I was saying above. Sorry for making myself not clear. So yea, I've replaced the col + 1 to col + 2 also changed the 'n-1' to n-2' as well. I am using the following syntax to put in the header of the columns. worksheet.update('P1', 'Automatically_Reserved') – himnaejah Jan 28 '22 at 05:30