Using Google API to batch delete rows after filtered

Questions
,
1

image
image1060×498 46.1 KB

I am working on housekeeping with files in Google Drive and in Google Forms (the spreadsheet element). So far I think I have Google Drive sorted and I have managed to collect all the rows needed for Excel clean up but I am not confident in the batchAPI command. Note this is a demo sheet so the number of rows in the data will not warrant the batch but in production there will be thousands every 6 months.

Delete data older than 6 months.blueprint.json (53.0 KB)

image
image533×806 30.9 KB

image
image566×650 27.2 KB

I have done lots of searching and whilst some mention the batchapi, noone shows a img of how to set it up.

Any help always gratefully received. I am getting more confident in building most of some of my automations now but still hitting hurdles.

3

Hi @newbuilder,

What is the goal you are trying to achieve? If you are trying to remove rows form the Google Sheets that match your filter, the best approach is a Search Rows module that contains the filter and as this returns bundles already, use a Delete Row module to remove each row.

Cheers,
Henk

4

That’s exactly what I need but on reading the forum, deleting 500 or so rows by that method is operationally intensive and an api call can do it. That is what I was needing a help with. Also if I delete rows will that affect other automations that look for addition of rows. Eg if I delete a row will the watch for new rows stop working?

Lots of questions - sorry

5

Don’t apologize for being curious, it will help you become an expert. Just like testing does.

You can use the Make an API Call module to generate your own bulk row delete by using https://sheets.googleapis.com/v4/spreadsheets/<spreadsheet_id>:batchUpdate and the following payload:

{
    "requests": {
        "deleteDimension": {
            "range": {
                "sheetId": 0,
                "endIndex": 5,
                "dimension": "ROWS",
                "startIndex": 4
            }
        }
    }
}

Here you can set the startIndex and endIndex to delete a range of rows. That means a from-to, which is not always correct of want to remove several separate rows.

If you delete a row, the ‘watch new rows’ module shouldn’t be triggered as there is no new row.

Cheers,
Henk

6

Thanks @Henk-Operative

just to clarify- how do i make the start and end index delete and miss some rows or is that not possible? Did I read somewhere that the index counts row 1 as zero so doesn’t match the user interface?

Also I didn’t explain re watch new rows.

If the watch new row triggered last because row 11 is populated and I delete rows 1-11, will it operate correctly when the new row 1 is populated or wait until row 12 is populated?

Many thanks for all the support

7

I would give you all the answers if I could, but I don’t have them. Test out what happens for re-watching rows. (I assume it is for every new row, regardless of whether you deleted it before)

EDIT: The range for row deletions is fixed, so you have to do multiple requests or somthing like this:

{
    "requests": [
        "deleteDimension": {
            "range": {
                "sheetId": 0,
                "endIndex": 5,
                "dimension": "ROWS",
                "startIndex": 4
            }
        },
        "deleteDimension": {
            "range": {
                "sheetId": 0,
                "endIndex": 8,
                "dimension": "ROWS",
                "startIndex": 7
            }
        }
    ]
}
8

If I do a search of rows with a criteria eg all rows that were created in December to march, then what becomes the inout to the api, is there a dynamic field generated?

Again sorry but brain is doing lots of if then else’s

9

Hi @newbuilder - just to add to what Henk was saying:

  • I believe watch modules will not re-trigger if rows are inserted in place where rows were deleted (I think it keeps an internal index of the last row number it checked), you’d need to test this
  • Make a watch module run every 5 minutes
  • Add some rows, wait for it to run, delete some rows, wait for it to run (should find nothing), add rows (the same way you normally would - via some automation or manually?) and see if it triggers

As for the ranges for deletion:

  • Google Sheets - search rows
  • Array aggregator - aggregate Row number
  • Google Sheets - Make an API call: use what Henk sent (remove the first part of the URL, this module’s URLs start from spreadsheets/{spreadsheetID}…
  • In startIndex, you’ll map your aggregator’s result as min( map( {ArrayAgg}; __ROW_NUMBER__ ) )
  • In endIndex, you’ll map the max value as sum( max( map( {ArrayAgg}; __ROW_NUMBER__ ) ); 1)

So if your Google Sheets search module returns eg rows 50-5000, the result of min() will be 50 (startIndex) and result of max() will be 5001 (max+1). endRange is exclusive so row 5001 won’t be deleted.

For non-contiguous rows, I wouldn’t advise doing that with a single batch request. It’s quite complex because you would need to map multiple ranges with formulas and aggregators, AND you would need to sort them from highest to lowest - because rows will shift positions if you start deleting “from the top”.

Cheers!

1 Like
10

Hi

you were correct, it does not see the deletion of rows as a reset of any indexing it has and fails to trigger again. This has totally made this full project redundant because I will need to do any clean ups and then a manual reset of the automation that it affects.

Unless the manual reset can be incorporated into this automation.

11

No, because it’s not the reset of the “watch rows” module that’s your issue :confused:

The issue is that spreadsheets don’t differentiate between content - row 20 is row 20 even when you delete it and create “a new row” in its place.

So not only are you trying to delete certain row positions (which will shift the positions of all rows in between and afterwards), you also want to do this on what sounds like a fairly active sheet which might be getting new rows as you’re deleting “old ones”.

I think your best bet for this particular sheet is to maybe use something like Baserow, Seatable or NocoDB.

12

You could also try using the “Watch Changes” trigger module.

14

Having manually tested it, it would require an automation that told another motivation to start from the beginning and then I would need to code that an email is only sent to inform the addressee that a new form had been created if the form date was the same as today?

A bit of a mess

15

Might be something in this if I coded any email to filter so that only forms with today’s date triggers email?

EDIT: However there is already a watch link in the spreadsheet for something else and linked to specific cells - I assume that you cannot have more than one URL