row.delete() not deleting row, instead is just formatting row

[Betanoir]

I am attempting to delete a row from my sheet entirely using the index from sheet.getRows(), instead of just clearing the values and clearing colour format. A little information: I am not using a form, instead, I am using a Discord bot to get responses from users, and posting their responses to the sheet using this library. I need the row to be deleted entirely as for this remove function, you are able to remove any rows, even ones with values on both sides of them.

Here is my code, please tell me if I have made a mistake, I have gone over all the documentation already, in attempt to check if I have missed another method I could use instead.

What I get after the function is run:

vs what I need: (The last row being deleted entirely)

[theoephraim]

The cell-based methods and row-based methods are not really designed to be used together. If you stick with row-based method calls, it should do the trick.

If you absolutely need to interact the the cell based methods (for example to deal with formatting) then after you delete the row, you must reload the cell data.

[Betanoir]

Apologies for the delay in response, but how would I reload the cell data correctly?

On Wed, Jan 20, 2021 at 2:47 AM Theo Ephraim notifications@github.com wrote:

The cell-based methods and row-based methods are not really designed to be used together. If you stick with row-based method calls, it should do the trick.

If you absolutely need to interact the the cell based methods (for example to deal with formatting) then after you delete the row, you must reload the cell data.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/theoephraim/node-google-spreadsheet/issues/430#issuecomment-763246400, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHLKKXTNW2J4XHGWFAOZESTS2YRYRANCNFSM4V4VOCHA .

[theoephraim]

So when you call sheet.loadCells, it loads a range of cells into a cache. Using the row-based API doesn't use or affect that cache - so if the rows have the data you need, you wouldnt even need to load the cells at all.

But if you do really need the cells, you can reload them by calling loadCells again, or potentially calling sheet.resetLocalCache() which would clear out that cache completely and then call loadcells again.

[Betanoir]

So basically, after I delete a row, I should reset the cache and then load the cells to do certain things?

On Tue, Jan 26, 2021 at 7:06 PM Theo Ephraim notifications@github.com wrote:

So when you call sheet.loadCells, it loads a range of cells into a cache. Using the row-based API doesn't use or affect that cache - so if the rows have the data you need, you wouldnt even need to load the cells at all.

But if you do really need the cells, you can reload them by calling loadCells again, or potentially calling sheet.resetLocalCache() https://theoephraim.github.io/node-google-spreadsheet/#/classes/google-spreadsheet-worksheet?id=fn-resetlocalcache which would clear out that cache completely and then call loadcells again.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/theoephraim/node-google-spreadsheet/issues/430#issuecomment-767687500, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHLKKXTOXTJIMMTWRAS7AJDS33ZDDANCNFSM4V4VOCHA .

[theoephraim]

What are you doing with the data? You may be able to just use the row-based methods and avoid cells altogether.

[Betanoir]

I'm using the cell-based method to load the values of specific cells so I can loop through them to see which one has, let's say, got the formula of '= 1+1', and if it has that formula, I push it to an array. Here is the code: [image: image.png]

On Tue, Jan 26, 2021 at 8:01 PM Theo Ephraim notifications@github.com wrote:

What are you doing with the data? You may be able to just use the row-based methods and avoid cells altogether.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/theoephraim/node-google-spreadsheet/issues/430#issuecomment-767722347, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHLKKXXIQ6RXB3PB4EJJDMDS337PPANCNFSM4V4VOCHA .

[Betanoir]

I tried using what you said to me, or at least what I understood, and weirdly enough, it still doesn't delete the row properly and instead just formats it... [image: image.png]

On Wed, Jan 27, 2021 at 11:34 AM Beta caseyharper2601.ch@gmail.com wrote:

I'm using the cell-based method to load the values of specific cells so I can loop through them to see which one has, let's say, got the formula of '= 1+1', and if it has that formula, I push it to an array. Here is the code: [image: image.png]

On Tue, Jan 26, 2021 at 8:01 PM Theo Ephraim notifications@github.com wrote:

What are you doing with the data? You may be able to just use the row-based methods and avoid cells altogether.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/theoephraim/node-google-spreadsheet/issues/430#issuecomment-767722347, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHLKKXXIQ6RXB3PB4EJJDMDS337PPANCNFSM4V4VOCHA .

[theoephraim]

What happens if you dont call any cell-based methods, just load rows, and then delete one?

[Betanoir]

It still didn't delete the row entirely, here is the updated code. [image: image.png]

On Thu, Jan 28, 2021 at 12:08 AM Theo Ephraim notifications@github.com wrote:

What happens if you dont call any cell-based methods, just load rows, and then delete one?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/theoephraim/node-google-spreadsheet/issues/430#issuecomment-768610974, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHLKKXUEEV4WK2GL3SSWMJLS4CFFPANCNFSM4V4VOCHA .

[theoephraim]

Not sure why, but your images aren't coming through :(

[Betanoir]

That's weird- Here's links for them:

1st: https://i.imgur.com/Ssaw29V.png 2nd: https://i.imgur.com/Csin2QC.png 3rd: https://i.imgur.com/ft9CQ0h.png

On Thu, Jan 28, 2021 at 12:52 AM Theo Ephraim notifications@github.com wrote:

Not sure why, but your images aren't coming through :(

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/theoephraim/node-google-spreadsheet/issues/430#issuecomment-768631528, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHLKKXWHIHEMQPECXPSRQV3S4CKMLANCNFSM4V4VOCHA .

[theoephraim]

Generally just one tip - if you set the first row in your sheet with meaningful headers ("userid", "username", "reason", "staff"), then when youre reading rows you get properties with those names (ex: row.userid)

To be honest, I'm really not sure what is going on. I just wrote a simple example to load rows and delete one and I do see it being removed completely. Are you sure there aren't some concurrent requests happening at the same time?

Also I'm not sure why youre calling loadInfo right before the delete - although it shouldn't affect anything.

[Betanoir]

That's interesting as mine doesn't- I am 100% sure that there aren't concurrent requests occurring at the same time.

I will try something tomorrow to see if it does, by chance, work then, although I highly doubt it will with my luck :(

Also, I added that as after loading the cells and getting what I required from them and resetting the cache, I had to call loadInfo again after doing that, in order to get the rows in the doc.

On Thu, Jan 28, 2021 at 1:50 AM Theo Ephraim notifications@github.com wrote:

Generally just one tip - if you set the first row in your sheet with meaningful headers ("userid", "username", "reason", "staff"), then when youre reading rows you get properties with those names (ex: row.userid)

To be honest, I'm really not sure what is going on. I just wrote a simple example to load rows and delete one and I do see it being removed completely. Are you sure there aren't some concurrent requests happening at the same time?

Also I'm not sure why youre calling loadInfo right before the delete - although it shouldn't affect anything.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/theoephraim/node-google-spreadsheet/issues/430#issuecomment-768654717, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHLKKXUO5LCPPV7PCKNVI5LS4CRD7ANCNFSM4V4VOCHA .

[Betanoir]

One thing I do feel the need to ask you is if there are multiple requests at once, but all are to do actions of different spreadsheets, would it be able to do this, or not? This is a requirement for me, so I am really hoping it's a yes..

On Thu, Jan 28, 2021 at 2:15 AM Beta caseyharper2601.ch@gmail.com wrote:

That's interesting as mine doesn't- I am 100% sure that there aren't concurrent requests occurring at the same time.

I will try something tomorrow to see if it does, by chance, work then, although I highly doubt it will with my luck :(

Also, I added that as after loading the cells and getting what I required from them and resetting the cache, I had to call loadInfo again after doing that, in order to get the rows in the doc.

On Thu, Jan 28, 2021 at 1:50 AM Theo Ephraim notifications@github.com wrote:

Generally just one tip - if you set the first row in your sheet with meaningful headers ("userid", "username", "reason", "staff"), then when youre reading rows you get properties with those names (ex: row.userid)

To be honest, I'm really not sure what is going on. I just wrote a simple example to load rows and delete one and I do see it being removed completely. Are you sure there aren't some concurrent requests happening at the same time?

Also I'm not sure why youre calling loadInfo right before the delete - although it shouldn't affect anything.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/theoephraim/node-google-spreadsheet/issues/430#issuecomment-768654717, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHLKKXUO5LCPPV7PCKNVI5LS4CRD7ANCNFSM4V4VOCHA .

[theoephraim]

Very odd. You can see that the row delete call internally is calling deleteRange

• see https://github.com/theoephraim/node-google-spreadsheet/blob/master/lib/GoogleSpreadsheetRow.js#L55
• see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#deleterangerequest

You could try calling that directly to the google API and see what happens. It could have something to do with how you have formatting set up, or maybe some merged cells?

I'm also curious what would happen if calling deleteDimension instead - although deleting a "range" that is a full row I'd think should should be equivalent to deleting a "dimension".

As for making concurrent requests, if they are on different sheets, no problem. If they are on the same sheets, I dont think there's anything stopping you but it could cause issues, as your local cache of the sheet data can get out of sync with the remote version. I asked since you could accidentally be deleting a row and then inserting or saving overlapping cells somehow at the same.

Generally the handling of row deletion needs work, as right now I dont think the row number of the other loaded rows gets updated. Should be relatively easy to fix that though.

When working with cells it can get tricky because we can't always infer what google's reply will be. For example when updating a formula, we dont know what the value will be.

[theoephraim]

One thing - if you want to give me access to the doc (or a copy of it with any sensitive info removed) I can take a look?

share with theozero@gmail.com

[Betanoir]

Alright, much obliged, I'll look into calling the google API in a bit!

As for giving you access, I have given you access to a sheet called "Copy of Initial Backend (Do not share)"

On Thu, Jan 28, 2021 at 2:25 AM Theo Ephraim notifications@github.com wrote:

Very odd. You can see that the row delete call internally is calling deleteRange

• see https://github.com/theoephraim/node-google-spreadsheet/blob/master/lib/GoogleSpreadsheetRow.js#L55
• see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#deleterangerequest

You could try calling that directly to the google API and see what happens. I'm also curious what would happen if calling deleteDimension instead - although deleting a "range" that is a full row I'd think should should be equivalent to deleting a "dimension".

As for making concurrent requests, if they are on different sheets, no problem. If they are on the same sheets, I dont think there's anything stopping you but it could cause issues, as your local cache of the sheet data can get out of sync with the remote version.

Generally the handling of row deletion needs work, as right now I dont think the row number of the other loaded rows gets updated. Should be relatively easy to fix that though.

When working with cells it can get tricky because we can't always infer what google's reply will be. For example when updating a formula, we dont know what the value will be.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/theoephraim/node-google-spreadsheet/issues/430#issuecomment-768667932, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHLKKXW3FCNSI3PB4PE25YLS4CVHNANCNFSM4V4VOCHA .

[theoephraim]

Closing for now - please reopen if you are still stuck!