Addition behaviour with insertRowByKey()

[lemunozm]

Hi! First of all, amazing library! just what I'm looking for 😃

I was playing with the insertRowByKey() (and its analogous insertColumnByKey()) in the case where I have empty rows in the worksheet. Giving an example:

	A	B	C
1	keyX	val1	val2
2
3	keyY	val1	val2
4

I found that if I add a new row using insertRowByKey(keyZ, [va1, val2]) The row is inserted in the fourth row instead of the empty second row. I do not know if this is the expected behaviour. Nevertheless, my use case implies a lot of additions and eliminations (rows cleared) and I found in a situation where almost all my worksheet is empty with the content lines spread out along the worksheet.

There is a way to get this behaviour using your API? In case not, could it be added under an optional parameter (as bool insertInFirstEmptyKeyRow? I wouldn't mind adding this feature as a PR if you agree.

EDIT: The same "issue" happens with append functions.

Thanks!

[a-marenkov]

Hi @lemunozm

Yes, it is expected behavior and now i see that in some cases can undesired.

Sure, PR's are welcome!

One thing to keep in mind that in case of appending multiple rows/columns it can be a little bit tricky.

Also i have one question - is it necessary to clear rows in your case? Can they be removed?

There is method for deleting rows that can be used await sheet.deleteRow(1);. There's no deleteRowByKey though...

Thanks!

[lemunozm]

Thanks for your answer!

I was thinking... and the deleteRow is a different point of view to the solution but fits perfectly for my problem. I do not know if it is more susceptible to concurrence problems, because deleting a row will shift the row's number of the following rows. But I understand that if I always access by "key" it should not be a problem.

Regarding the addition of multiple rows, my ideal behaviour will be that each row is appended to each empty row found in the worksheet. I was investigating the google sheet API and seems that this is not possible. If I append 100 rows, and the worksheet found an empty row, it will insert the 100 rows together from the first empty row found overwriting the following 99 rows. Totally undesired. And do it manually will consume all the quotes because it needs to be independent requests.

Seems like deleteRow is the best solution here and force to keep all the data always together without cleaned rows.

Thanks for the suggestion!

[a-marenkov]

Hi @lemunozm

Be careful with concurrence problems - gsheets is not designed to manage them (even you access cells by 'key'). You can use something like https://pub.dev/packages/synchronized to synchronize the operations. I thought of handling it internally, but didn't try it yet.

[lemunozm]

Hi @a-marenkov,

With concurrence, I was referring to several applications using the same google sheet. I understand that if someone removes a line, and another user edits some line by key, there is some minor time interval while data can be corrupted.

Anyway, I took a look at the synchronized package and seems like a must-have for my application, so thanks for the advice of using it :).