Add support for append columns

[mkismy]

Currently there are append_rows / append_row method but we need something like append_cols. There a real difficulty to append column data.

The current steps are to get cell range from the last column, update each cells, update the worksheet.
If the sheet does not have enough columns, APIError are returned and we have to insert columns then try it all over again.

[alifeee]

the related functions we have are

• worksheet.append_row (which calls append_rows([row]))
• worksheet.append_rows
• worksheet.insert_row (which calls insert_rows([row]))
• worksheet.insert_rows
• worksheet.insert_cols

Does worksheet.insert_cols fit your needs?

[mkismy]

No it does not.

	A	B
1
2

If we want to append data to a new column C which does not exist in the spreadsheet, and use insert_cols, I assume it would be something like this and it will raise error.
It is the same thing when inserting data to a new row 3.

sheet.insert_cols([values], 3)
sheet.insert_rows([values], 3)

APIError: [400]: Invalid requests[0].insertDimension: range.startIndex must be less than the grid size (2) if inheritFromBefore is false.

---

Widens the worksheet if there are more values than columns.

append_rows has this behavior where it will automatically add new rows to match values if the target row does not exist and this is what is needed for columns too.

sheet.append_rows([values])
→ Succeed to add values to newly created row 3

[lavigne958]

This is a nice feature !

I ran some tests and the google API does not behave exactly what we expect it to 😞

This is the sheet I start with:

	A	B
1
2

I tries the API endpoint to append values (the exact same one as Worksheet.append_rows()

I got the following result:

	A	B
1	X
2	Y

it did take the list of values as a column, and append the value on a column (meaning first value in the list goes to the top of the column and the rest follow bellow on the same column).
Though: the API appends the value on the first empty column it finds.... Fine, be it, may be it appends column from the left-most cell with values.

So I ran the exact same code again and got:

	A	B
1	X
2	Y
3	X
4	Y

🤦 alright, in fact the API appends the values bellow any existing values, creating the necessary rows if needed, but always bellow on the next row anyway...

I managed to get the following result:

	A	B	C
1	X		X
2	Y		Y
3	X
4	Y

but I had to set the column myself in the requested range, which is the opposite of what we want. The idea of Worksheet.append_rows() is: I don't know the size of my spreadsheet, I just want to append values below the last row and I want new rows to be created if necessary.

If this feature is still useful to then we can plan it to the next minor release. just let us know 🙃

[mkismy]

The idea of Worksheet.append_rows() is: I don't know the size of my spreadsheet, I just want to append values below the last row and I want new rows to be created if necessary.

If the append_cols() could be the equivalent to this behavior it would be appreciated:
I just want to append values right to the last column (with values) and I want new columns to be created if necessary

If we need to specify the range ourself, I think we can just leave it until we have a better solution.

[lavigne958]

The idea of Worksheet.append_rows() is: I don't know the size of my spreadsheet, I just want to append values below the last row and I want new rows to be created if necessary.

If the append_cols() could be the equivalent to this behavior it would be appreciated: I just want to append values right to the last column (with values) and I want new columns to be created if necessary

I understand, that's what I tried to do and it does not work this way 😞

If we need to specify the range ourself, I think we can just leave it until we have a better solution.

I understand, then we'll leave it like this for now. I checked again and the google sheet API documentation is clear:

values are appended after the last row of the table

closing this issue for now, until we find a better way.