Google Sheets Formula for value that isn't yet there

Question

I'm trying to create a formula that will look for a number within a string of text that will be occurring in a random cell but hasn't occurred yet (the value shows up in the spreadsheet once someone picks an option from the Google Form). I am able to make the formula work just fine once the value appears, but I need it to be able to look for the value before it appears. Not sure how to explain it better, but here's a scenario:

The user is able to choose between 50 choices on a Google Form. They pick the choice "Item 203". "Item 203" is then added to the spreadsheet and is counted the number of times it is chosen. When "Item 203" reaches being selected 20 times an email is sent to a designated person.

My problem is that sometimes all items aren't chosen so "Item 203" doesn't always show up in the same cell. I can't seem to figure it out. If anyone has any suggestions that would help a lot. Thanks!

EDIT: If the value isn't there then it doesn't do anything. It only look for number part of the items. So if there was "Item 203, Item 204, Item 205, etc." I need to make the formula count how many times 203 occurs and if at 20 send email. Then I need to do same thing for Item 204 only thing different is the recipient of the email and so on and so forth. I'm trying to make a formula that will work now, but not quite there. If I get one that works or at least somewhat works I'll post it.

This is the formula I use to count the items in Column C.

=ArrayFormula(QUERY(C1:C3000&{"",""},"select Col1, count(Col2) where Col1 != '' group by Col1 label count(Col2) 'Count'",1))

I want something like this to happen. My problem is that I won't know what cell Item 203 ends up in because people won't be choosing the same items everyday. I do know that I want to send the email at a value of 20.

// Fetch the email address 
var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("B2"); 
var emailAddress = emailRange.getValues();
// Send Alert Email. 
var message = 'This is your Alert email!';
// Second column 
var subject = 'Your Google Spreadsheet Alert'; 
MailApp.sendEmail(emailAddress, subject, message);

The "getRange("B2") doesn't work for me because Item 203 doesn't always end up in cell B2.

EDIT 2: This is the script that I currently have. Right now when I click submit on the form it emails me because I have an auto trigger set to run the script every time the form is submitted. I want it to be able to wait to email me until a condition for "Item 203" is met (or any Item # as well). I want it to email someone else when "Item 204" condition is met. If multiple conditions are met at the same time i.e: "Item 203 & 204 both reach 20 selections" then it emails me for 203 and someone else for 204. The logging right now works but I have to manually run the script to get it to work.

function autoEmail(){
      sendEmail();
      myFunction();
}
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Daily 
Count"); 
  var data = sheet.getDataRange().getDisplayValues();
  var total = {"Item 203": 0, "Item 204": 0};

  for(var i=0; i<data.length ; i++){
    var row = data[i];  

    for(var j=0; j<row.length ; j++) {
      var cell = row[j];

      for(item in total) {
        var replace = item;
        var re = new RegExp(replace,"g");
        var count = (cell.match(re) || []).length;
        total[replace] += count;
      }
    }    
  }
  Logger.log(total);
}
function sendEmail() {
  // Fetch the email address 
var emailRange = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Daily 
Count").getRange("H4"); 
var emailAddress = emailRange.getValues();
// Send Alert Email. 
var message = 'My test email.';
// Second column 
var subject = 'This is a test.'; 
MailApp.sendEmail(emailAddress, subject, message);
}

What it's supposed that should be the result if the value isn't there? What is the formula? In other words, please add a minimal reproducible example. — Rubén

Andres Duarte Andres Duarte · Accepted Answer · 2019-08-23T10:00:16

You can use the getDataRange() [1] function that will retrieve you the complete range where there's data inside the sheet. You can search for the item string in each cell and add the occurrences for each item in a JSON object.

Here is the code I implemented, which will search in the entire responses sheet for each attribute (items) from the "total" JSON.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form responses 1"); 
  var data = sheet.getDataRange().getDisplayValues();
  var total = {"Item 203": 0, "Item 204": 0};

  for(var i=0; i<data.length ; i++){
    var row = data[i];  

    for(var j=0; j<row.length ; j++) {
      var cell = row[j];

      for(item in total) {
        var replace = item;
        var re = new RegExp(replace,"g");
        var count = (cell.match(re) || []).length;
        total[replace] += count;
      }
    }    
  }
  Logger.log(total);
}

[1] https://developers.google.com/apps-script/reference/spreadsheet/sheet#getdatarange

Google Sheets Formula for value that isn't yet there

1 Answers