Count the amount of times a number appears in a column

[Adam_Kramer]

Hi all,

I have three columns: Member Number, Referrer, and Referrals. I am trying to count how many records contain the Member Number in Referrer, for the Referrals field. In other words, if I have 100 member records, and 5 records have “24” in the Referrer field, I would want the Referrals field in Member Number 24’s record to say 5 (the amount of times they reffered members).

Does this make sense? Any ideas on how to do this?

Thanks so much!
Adam

[Bill_French]

Richard_Foxworthy:

Could you please provide any comentary on how this might be set up?

Sure. To do what I did here, you’ll need the script running in a Google Account (either a free Gmail account or a G-Suite account), This will show you how to create a Google Apps Script project which provides a place for the shared script to run.

In my view, this scripted process needs to run every five minutes to tally the totals and update them into Airtable. There doesn’t need to be any dependencies on Zapier - to runs when it does and this script is simply looking at the latest data and performing the computations.

It’s all free and you’ll be amazed once you get something simple runnin.

[Richard_Foxwort]

Thanks Bill - I appreciate your help.

For my application I have a bunch of things that happen when a new record is written to the Users table, and it all revolves around zapier automations, so I really want this part to be triggered in the same way, rather than on a time basis. I can probably figure out how to make that happen once I actually get the script working by using zapier to call the URL of this script deployed as a web app.

My problem right now is I have not been able to make the script work. Your screenshot starts at line 11 and does not run as is - I’m a complete noob in this stuff but seems clear to me that the script will need to start wih something like:

var Airtable = require('airtable');
Airtable.configure({
    endpointUrl: 'https://api.airtable.com',
    apiKey: 'YOUR_API_KEY'
});
var base = Airtable.base('BASE_KEY');

Thanks again for any further help!

Cheers

[Bill_French]

Okay - I see where you’re hitting a wall.

Richard_Foxworthy:

… once I actually get the script working by using zapier to call the URL of this script deployed as a web app.

Calling the URL of any script deployed as a web app requires a web service, not a timed script that executes as I have mentioned before. A web service like this must be deployed to listen for calls from Zapier and it must be publicly accessible and preferably uses an authorization token of some type to avoid POSTs from bad actors and hacker bots.

You can create just such a service using the doPost() method for the ContentService SDK in Google Apps Script. This is probably the best tutorial for this activity.

function doPost(e) {
  // do some stuff with Airtable data...
  return ContentService.createTextOutput("200");
}

In the spot where I indicated, you would use Google Apps Script and the Airtable API to perform some operations to recalculate fields across the rows.

And since Google Apps Script is a blocking architecture, you needn’t actually return the HTTP response code of 200 because you don’t really care about letting Zapier know when the process completes. Rather, you just want Zapier to kick this process off and let it run until complete. As such, the return() statement isn’t even needed.

Essentialy, you are chaining webhooks where this Google Apps Script webhook server will be called by the first webhook server (Zapier). The endpoint of your Google Apps Script webhook server will become apparent as soon as you run through the tutorial and deploy the server. That URL (trails with “…/exec”) will be what you have Zapier call and pas to it a payload of instructions (or not). It you just want it to run, you don’t have to pass anything to it.

[Bill_French]

Richard_Foxworthy:

I have not been able to make the script work.

This part is entirely separate from the above response discussing a webhook server that listens for calls from Zapier. The code I published doesn’t disclose all of the underlying API processes necessary to retrieve and update Airtable records. Below are those functions and you are also free to use the javascript SDK instead.

//
// get airtable (universal)
//
function atGetTable_(baseKey, tableName, logit)
{
  logit = (logit == undefined) ? false : logit;
  const options = {
    method: 'GET',
    headers: {
      'Authorization' : 'Bearer ' + airtableAPIKey,
      'Content-type': 'application/json'
    },
    muteHttpExceptions : true
  };
  var response = UrlFetchApp.fetch(airtableAPIEndpoint + baseKey + "/" + encodeURIComponent(tableName), options).getContentText();
  if (logit)
    Logger.log(response);
  return(response);
}

//
// update airtable (universal)
//
function atUpdateTable_(baseKey, tableName, payload, apiKey)
{
  apiKey = (apiKey == undefined) ? airtableAPIKey : apiKey;
  var options = 
      {
        method: 'PATCH',
        headers: {
          'Authorization' : 'Bearer ' + airtableAPIKey,
          'Content-Type'  : 'application/json'
        },
        payload : JSON.stringify(payload),
        muteHttpExceptions : true,
        followRedirects: true
      };
  // Logger.log(airtableAPIEndpoint + baseKey + "/" + encodeURIComponent(tableName));
  var response = UrlFetchApp.fetch(airtableAPIEndpoint + baseKey + "/" + encodeURIComponent(tableName), options).getContentText();
  return(response);
}

[Richard_Foxwort]

Thanks Bill, I really appreciate your help.

I’ll let you know how I go with putting these pieces together!

[Rob_Tucker]

This seems like such a simple task but in practice is ridiculously complex. is this still the case three years on? As I am looking for the same solution. I am currently looking to move some excel data to airtable but it is so far not near impossible. Simple things like ‘CountIf’ require hugely complex external scripts.