Clearing and Deleting Rows in Google Sheets

The Google Sheets connector in Workato has several out of the box actions such searching, updating, and adding rows but there is nothing for deleting or clearing rows. This can be accomplished with a custom action. As with anytime you are deleting data, make sure you are careful with the implementation so you don’t discard any valuable data.

Clearing Rows

You can add a Google Sheets action step as you normally would by selecting the Google Sheets App but at the bottom, below the ‘All Actions’ section, you will see a box called Custom Action that you need to click.

Then follow these steps:

1. Give your action a name
2. Method is POST
3. The Path is spreadsheets/[spreadsheet ID]/values/SheetName!R2C1:R10000C5:clear

You need to replace the spreadsheet ID with either the hardcoded value, or if you are referencing that spreadsheet in an earlier step you can also use a datapill. The bolded section above contains the range you want to clear, starting from the top left all the way to the bottom right.

In my example, I am clearing everything from row 2, column 1(A) all the way to row 10,000 and column 5(E).

If I had data that I wanted to clear from row 2, column A all the way to row 75, column M this would be R2C1:R75C13.

Deleting Rows

Similarly, you can use a Google Sheets Custom Action to delete rows. I use this instead of clearing because sometimes I only want to remove specific records without having blank rows. The setup for this is:

1. Give your action a name
2. Method is POST
3. The Path is spreadsheets/[spreadsheet ID]:batchUpdate

Input JSON

Then you need to paste this JSON in the body request parameters:

[
  {
    "name": "requests",
    "type": "array",
    "of": "object",
    "label": "Requests",
    "details": {
      "real_name": "requests"
    },
    "properties": [
      {
        "properties": [
          {
            "properties": [
              {
                "control_type": "number",
                "label": "Sheet ID",
                "parse_output": "float_conversion",
                "type": "number",
                "name": "sheetId",
                "details": {
                  "real_name": "sheetId"
                }
              },
              {
                "control_type": "text",
                "label": "Dimension",
                "type": "string",
                "name": "dimension",
                "details": {
                  "real_name": "dimension"
                }
              },
              {
                "control_type": "number",
                "label": "Start index",
                "parse_output": "float_conversion",
                "type": "number",
                "name": "startIndex",
                "details": {
                  "real_name": "startIndex"
                }
              },
              {
                "control_type": "number",
                "label": "End index",
                "parse_output": "float_conversion",
                "type": "number",
                "name": "endIndex",
                "details": {
                  "real_name": "endIndex"
                }
              }
            ],
            "label": "Range",
            "type": "object",
            "name": "range",
            "details": {
              "real_name": "range"
            }
          }
        ],
        "label": "Delete dimension",
        "type": "object",
        "name": "deleteDimension",
        "details": {
          "real_name": "deleteDimension"
        }
      }
    ]
  }
]

This will give you four input boxes.

• Sheet ID: The spreadsheet ID or use a datapill
• Dimension: ROWS
• Start Index: The row number preceding the row you want to delete. For example, if I want to delete row 24, this would be 23. I do this by using a formula and adding a row number data pill and subtracting 1.
• End Index:  The row you want to delete

Iteration

Special caution should be used if you are going to use a For Each action when deleting rows because your index may get thrown off. For instance, if you search rows matching a criteria that you want to delete, it will get the row numbers right away so after you delete the first row, the next row will be a row higher than when you did the search. To solve this, above the search action I create an integer variable called ‘row_offet’ and assign the value as 0. The formula for the index boxes are now:

• Start Index: [row number from for each step] – [row_offet] – 1
• End Index: [row number from for each step] – [row_offet]

I then have an action step to add 1 to the row_offset variable so that the next time it runs, it will subtract 1 from each row since deleting the previous row moved all the rows up 1.