row.Delete() is sometime not deleting the row as expected

[romain130492]

Hello!

Im using the row API with the delete() method : https://theoephraim.github.io/node-google-spreadsheet/#/classes/google-spreadsheet-row?id=fn-delete

 async deleteAllRow(sheet){
    try {
      if(!sheet){
        throw Error("sheet is null")
      }
      const rows = await sheet.getRows();
      console.info(`🗑️🗑️🗑️ Deletion is in progress rows 🗑️🗑️🗑️`)
      for (let indexRow = 0; indexRow < rows.length; indexRow++) {
        console.log(indexRow,'the index row now');
        const row = rows[indexRow];
        console.log(row['database id'],'database id');
        await row.delete()
      }    
      console.info(`🗑️🗑️🗑️ Deleted rows 🗑️🗑️🗑️`)
      console.log('sheet rows delete');  
      return 
    } catch (error) {
      throw Error(`deleteAllRow() error: ${error}`)
    }
  }

The issue is that its deleting a few rows but not all of them as expected. For example if I have 5 rows, it will delete the 1 and 3, not the 2,4,5, even though I can see the ID of 2,4,5 in my log(in the loop) Any ideas @theoephraim ? thanks!

[WVAviator]

I had a similar issue calling .addRow() inside a for loop like that. I think Google might be throttling API requests. In my case calling addRows() instead worked. I don't think there's a similar solution for deleting rows.

[CodeForcer]

I'm getting the same issue. If I loop through rows it only deletes about half of them. I even tried putting some sleeps in the loop and that didn't help.

Not sure it's a throttle issue as I should be within API limits

[theoephraim]

sorry for the delay folks. This module definitely does not handle deletion of rows as well as it could... Unfortunately rows dont have a unique ID we can refer to - we just have the row number - which means we must attempt to keep our local cache of data in sync and I don't believe I've handled that well (if at all).

To make matters more complicated, sending requests quickly means that the remote data may not be exactly in the same state yet and the google API can be a bit unpredictable how it will handle things.

I hope to tackle this issue more thoroughly in an upcoming major rewrite, but not sure when it will happen...

[Zaphod-Beeblebrox]

Just adding a voice to this. I too am having very unpredictable results with .delete(). any other suggestions to get around this?

[theoephraim]

Obviously this wont work for all use cases, but one option for some is to use a "soft delete" - meaning add a is_deleted/deleted_at column to the table and then filter out those "deleted" rows within your application code.

You could occasionally migrate those rows to another sheet (even manually) to help with performance issues of loading the extra rows.

[Zaphod-Beeblebrox]

Obviously this wont work for all use cases, but one option for some is to use a "soft delete" - meaning add a is_deleted/deleted_at column to the table and then filter out those "deleted" rows within your application code.

You could occasionally migrate those rows to another sheet (even manually) to help with performance issues of loading the extra rows.

Thanks for the quick reply and suggestion. it's actually something along these lines that led me to this issue.
My code compiles a list of issues to be looked at by a human. Once completed, they set the 'Resolved' column to true ( I want to make this a checkbox, but that' another issue entirely. I understand that may not be possible with this package).

My code sweeps the resolved rows into a second tab(for archive purposes), and then deletes the row from the initial tab/.. and this is where it is failing.

[theoephraim]

I've published a new update which improves the handling of deleted rows. Now a cache of rows is tracked, and if a row is deleted, all later rows have their row indexes adjusted accordingly. It's still probably not perfect, but it should work for most cases!

Please test it out and open new issues if you run into any problems.

[silvalucas9031]

I have the same problem and after I delete it, it deletes some and crashes the worksheet and I get this error

error: { code: 503, message: 'The service is currently unavailable.', status: 'UNAVAILABLE' }