Count Items in Google Sheet

Hi,

I have a google sheet with a column called “INFO”. I want to count how many rows in this column contain the value 1 and enter this total in cell E1. How would I go about this?

Thanks.

Hey @tamone!

Welcome to the community :tada:

I have build a small workflow that might help you with your use case. Please note that the Google Sheet node doesn’t have the functionality to add data to a specific cell. Your column should have a name which allows the node to map the information to the correct cell.
Here’s the sample workflow:

{
  "nodes": [
    {
      "parameters": {
        "authentication": "oAuth2",
        "operation": "lookup",
        "sheetId": "1a6zypYHXsjzpw3sUjTEgQhlYGkvgPkIAquSs2kmopFI",
        "lookupColumn": "Name",
        "lookupValue": "1",
        "options": {
          "returnAllMatches": true
        }
      },
      "name": "Google Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        660,
        980
      ],
      "credentials": {
        "googleSheetsOAuth2Api": "Google Sheets @n8n"
      }
    },
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "string": [
            {
              "name": "Total",
              "value": "={{$items.length}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set1",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        860,
        980
      ],
      "executeOnce": true
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "operation": "append",
        "sheetId": "={{$node[\"Google Sheets\"].parameter[\"sheetId\"]}}",
        "options": {}
      },
      "name": "Google Sheets1",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        1060,
        980
      ],
      "credentials": {
        "googleSheetsOAuth2Api": "Google Sheets @n8n"
      }
    }
  ],
  "connections": {
    "Google Sheets": {
      "main": [
        [
          {
            "node": "Set1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set1": {
      "main": [
        [
          {
            "node": "Google Sheets1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

You can copy and paste the workflow in the Editor UI. Make sure to configure the credentials and update the spreadsheet ID.

1 Like

Hi,

The “Total” is always 3 regardless of how many Results the Google Sheets Lookup finds.

Do I need to somehow refer the {{$items.length}} back to this node?

Thanks.

Hey @tamone,

I found the solution! The expression we were using in the Set node earlier was incorrect. You can use the below Set node to get the length of the items.

{
  "nodes": [
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "string": [
            {
              "name": "Total",
              "value": "={{$items('Google Sheets').length}}"
            }
          ],
          "number": []
        },
        "options": {}
      },
      "name": "Set1",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1060,
        360
      ],
      "executeOnce": true
    }
  ],
  "connections": {}
}
1 Like

Great - that works, thank you!

Is it possible to do a lookup that checks 2 columns? e.g. lookup all rows that have Name = 1 AND Type = 3?

Thanks.