Trying to delete a large number of rows trough API

geraldo16
geraldo16 ✭
edited 12/09/19 in API & Developers

Hi

I am using the Node JS SDK to delete the content from a sheet. It works fine but for a lot of rows it fails (I have tested with a minimun of 1000). I get an error that says that the request is to large for the server. I understand whats happening, but I was wondering if there is another way to clear a sheet using the API. 

This is the error

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">\n<html><head>\n<title>414 Request-URI Too Large</title>\n</head><body>\n<h1>Request-URI Too Large</h1>\n<p>The requested URL's length exceeds the capacity\nlimit for this server.<br />\n</p>\n</body></html>

Tags:
· Share on FacebookShare on Twitter

Comments

  • dAVE Inden
    dAVE Inden Employee

    The request to delete rows adds the row ids as query strings to the URL. Since row ids are 16 digits long if you provide 1000 row ids at once it will create a URL that is over 16,000 characters. Typically URLs that are this large will fail with this error due to too many characters.

    I suggest breaking up your list into smaller chunks, ~100-200, and looping through it to send multiple delete requests to the sheet instead of sending them all at once. You may need to tweak the number of row ids to include to get as many deleted in one request.

    · Share on FacebookShare on Twitter
  • geraldo16
    geraldo16 ✭

    The problem with this solution is that if I try to send several delete request to the API it will reply that there is a request to the same sheet been processed, so I need to figure out how to wait till the end of every request to start the other. I was looking an alternative using the API that alows me to clear the sheet or something like that.

    · Share on FacebookShare on Twitter
  • dAVE Inden
    dAVE Inden Employee

    Yes, with the looping process you would need to wait until you get a response back from the previous request before moving on to the next one to keep from getting that error.

    · Share on FacebookShare on Twitter
  • geraldo16
    geraldo16 ✭

    The solution was that, breaking the total of rows in small pieces (200 rows per piece), and then passing the request, one by one. I had to wait for a request to end to send the next request. So i did a for cicle, and using await I turned the asyncronous behavior of javascripts into syncronus. Working great so far, I was even prepared for a decrease in performance but I tested this method with 5000 rows and worked fine and fast. Maybe in the future in would be great if we could delete several rows by passing an array of elements with the ids to delete, using a post method. Will be better in my opinion, and also similar for example to the process where you insert several rows.

    · Share on FacebookShare on Twitter
  • Ian Macintosh
    Ian Macintosh ✭✭

    Any chance you could give us some sample code please? I have been battling the syntax for a day and just can't get my head around it :-(

    · Share on FacebookShare on Twitter

Categories