Delete rows based on values within cell, optimising


I have script running on a weekly bases deleting rows from a document and then pasting these values to another sheet for data analysis, however as the document grows (+20,0000 rows) my script times out. Anyway on how to optimise the script to perhaps use less processing/memory and run faster?

var rowsDeleted = 0; for (var i = 0; i <= numRows - 1; i++) { var row = values[i]; if (row[0] == 'delete' || row[0] == '') { data.deleteRow((parseInt(i)+1) - rowsDeleted); rowsDeleted++; } }

In your situation, I understand as follows.

<ul><li>There are a lot of rows which are deleted.</li> <li>The rows are not continued. So the rows have to be deleted row by row using deleteRow().</li> </ul>

If my understanding is correct, how about this modification?

<h3>Modification points :</h3> <ul><li>Use batchUpdate of Sheets API. <ul><li>By using Sheets API, the discrete rows can be deleted by one API call.</li> </ul></li> <li>Create the list of rows which are deleted as the request body for Sheets API.</li> <li>Delete the rows by batchUpdate of Sheets API with the request body.</li> </ul>

In order to use Sheets API, please enable Sheets API at Advanced Google Services and API console. About the how to enable them, please check here.

<h3>Modified script :</h3> var spreadsheetId = "#####"; // Please input spreadsheet ID. var sheetId = "#####"; // Please input sheet ID. var deleteRows = []; for (var i = values.length - 1; i >= 0; i--) { if (values[i] == "delete" || values[i] == "") { deleteRows.push({ "deleteRange": { "shiftDimension": "ROWS", "range": { "startRowIndex": i, "endRowIndex": i + 1, "sheetId": sheetId } } }); } } Sheets.Spreadsheets.batchUpdate({"requests": deleteRows}, spreadsheetId); <h3>Note :</h3> <ul><li>In this modified script, it supposes that the retrieved values from Spreadsheet is in values.</li> <li>Before you run this, please input spreadsheet ID and sheet ID having the rows you want to delete.</li> </ul>

If this was not result what you want, I'm sorry.



var result = values.filter(row => row[0] !== 'delete' && row[0] !== ''); var rowsDeleted = values.length - result.length;



  • VS Code extension: Hide commands from command palette
  • Can async-await be available in other .NET languages besides C#?
  • Achieving `call_user_func_array` in Ruby
  • Could not load file or assembly 'Microsoft.AspNet.Security.DataProtection'
  • Converting Decimal to Hex
  • Logging flyway sql with Spring Boot
  • Unable to count the number of matches in Vim
  • How to access Safari history from iPhone App using Private Frameworks
  • Images tile on Google map in android
  • Can the use of C++11's 'auto' deteriorate performance or even break the code?
  • Speed up Date#parse & Date#strptime in Ruby, more elegant way or best practice?
  • Google Charts: Animation doesn't work for one chart, pointing annotations in the title area
  • iOS: Address Book
  • How to delete first 7 characters of folder name by using batch script?
  • Get last 15 lines from a large file in SFTP with phpseclib [duplicate]
  • How to escape xpath in php
  • How to modify the way a ForeignKey field is rendered in a Django admin page to avoid browser crash?
  • How do I change the names of buttons on a message box?
  • Unity Resources.load() won't work with external dll
  • C++11: Why rvalue reference parameter implicitly converted to lvalue
  • Shiny - change the size (padding?) of dropdown menu (select tags) smaller
  • How to check if a database and tables exist in sql server in a vb .net project?
  • java.io.FileNotFoundException: Could not open ServletContext resource [/WEB-INF/SpringDispatcher-ser
  • Add font awesome icon to custom add to cart button in Woocommerce 3
  • Django, uWSGI & nginx: Process dies for “no reason”
  • Rotating Towards Path in OpenGL
  • gnuplot - How to make zmin equal to zmax keeeping autoscale on z axis
  • C++ STL stack pop operation giving segmentation fault
  • How to configure the APNS.Certificate in the arm template
  • Change cell value based on cell color in google spreadsheet
  • XEP-0166: Jingle protocol implementation for voice/video chat in iOS
  • What does the “id” field in an Android “Google Play Music” broadcast intent correspond to?