How to Put Formula Count another sheet in App Script

Question

my First ASK and last at 2020. Im Newbie with google App Script, need some help I had 2 Spreadsheet

Spreadsheet ssGETDATA with SHEET1 column(id,name,address,ts,visit)
Spreadsheet ssVERIFY with SHEET1 column (id,ts)

I send data from Android use this code :

function doGet(e) {
 return message("Error: Please Try Again");
        }
  
function doPost(e) {
var id= e.parameters.id[0];
var name= e.parameters.name[0];
var address= e.parameters.address[0];
var visit= e.parameters.visit[0];
        
      var ts = Utilities.formatDate(new Date(), "GMT+8", "dd/MM/yyyy HH:mm:ss");
      var ss = SpreadsheetApp.getActive();
      var sh = ss.getSheets()[0];
      sh.appendRow([id,name,address,ts,visit]);
      
      return message("Data Saved");
    } else {
      return message("Error: Please Try Again");
    }}}

function message(msg) {
  return ContentService.createTextOutput(msg);
}

I want to Verify data from ssGETDATA but data (id) appeared several times. so my idea every time append row executed it put formula in column (visit) with =count(id,ssVERIFYSheet1!id) to check it Verified or Not

how it applies in the google app script?

i hope when ssVERIFY changed then ssGETDATA column (visit) counting too.

thanks for your explanation. Happy new Year

This was solved on the App Inventor forum, introducing a formula to each row of the spreadsheet that did the comparison, using google apps script community.appinventor.mit.edu/t/… — Metric Rat

Martí Martí · Accepted Answer · 2020-12-31T13:01:09

The simplest way to check if a column has a value probably is using getValues(), and search for the row in the array:

const range = sheet.getRange('A2:A')
const hasBeenValidated = range.getValues()
  .flat()
  .some(value => value === id)

if (hasBeenValidated) {
 // [...]
}

Notice that I assumed that id is on column A and that it has a header.

flat() is necessary because getValues() returns a 2D array (array of rows) and we only need an array of values.

With this snippet you can check for existing entries at ssGETDATA and/or ssVERIFY before making any changes. It should work for any column but you may need to change the value === id part depending on the type of it (dates, for example).

How to Put Formula Count another sheet in App Script

1 Answers

References