Solved: Re: How to pass params to HYPERLINK formula

sayan1 · 08-13-2020 08:53 AM

Hi, friends!

I have making cattle counter app. When cow calved, I can’t catch them in that time. In many cases I haven’t tool for tagging them on a land. But birthday of calve is very important data for my system. So I have give the new childe temprorary ID. It’s negative number of calves mom.
For example if cow 60 calved I have give them just -60. And it’s work. This is uniq ID.
When I catch calve in farm I have give them new uniq ID, like 219. But I have make them in manualy, from Google spreadsheet.

This is long proccess for me and I want to make something like form submitting.

So I need to get from 2 inputs and pass them to HYPERLINK formula, that call my GAS API and call function that change ID.

ID - for appsheet is uniq, that not changeable, but for Google Spreadsheet it’s just cell value. After change this value I need just reload appsheet app.

Any idea ?

sayan1

I have maked them right now. And it was very interesting task.

AppSheet and Gas is very good tool.

So, first of all, I have make table with two columns. First column is for old ID and second for new.

In appSheet column setting make type of old ID ref type. Now, I can select ID from drop down list. And second column setting don’t change.

After that, I have make action that open external link and I am use formula hyperlink.

This is example of formula:

HYPERLINK(CONCATENATE("https://script.google.com/macros/s/AIpcX-pSp73kHE-CbgbYzKfhVOCf8/exec?", "find=", [OldID], "&repl=", [NewID]), "")

And on ther server side of GAS, make this script that replace ID and this code looks like this:

function doGet(request) {
  var result = "Бір жерден қате кетті";
  if(request.parameter.find && request.parameter.repl){
    result = "Сырға ауыстырылды: "+changeVID(Number(request.parameter.find), Number(request.parameter.repl));
  }else if(request.parameter.resetcounter){
    clearMarked();
    result = "Санақ қайта басталды";
  }
  return ContentService
   .createTextOutput(result)
   .setMimeType(ContentService.MimeType.TEXT)   
}

In this code, I have check by parameters wich function needs to call.
if(request.parameter.find && request.parameter.repl)

After that, I have call function that find and replace ID in my column and replace them to new.
this code I have found on stackoverflow:

function tryChangeVID(f,r, table, col) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(table);
  var range = sheet.getRange(col);
  var values = range.getValues();
  var res = f;
  
  for (var i = 0; i < values.length; i++) {
    if (values[i][0] === f) {
      values[i][0] = r;
      res = r;
    }
  }
  
  range.setValues(values);
  
  return res;
}

function changeVID(find, repl){
  var result = tryChangeVID(find, repl, "Animals", "A:A");
  
  if(result === repl){
    result = tryChangeVID(find, repl, "Calves", "C:C");
  }
  
  return result;
}

This function works like webhook, without any response. But this good desition for my task.

Maybe someone help

View solution in original post

Grant_Stead

In my opinion you should use a different data structure… You should have a table for cattle, a table for tags, and a table for pairings in which you connect the cow to the tag. This is better because if the tag get’s compromised and needs to be replaced you don’t change the ID of the cow itself… Sample App - NFC / Barcode / Geolocation and Foreign Keys

Grant_Stead

In your case you probably would not need the third parings table… the tag table will do that job…

Steve

Why not instead copy the temporary row’s values into a permanent row once you get the tag, the delete the temporary row?

Grant_Stead

That’s a good idea as well… My only thought is that if he’s interacting with that cow in any other way, then changing the key, or starting a new record is pretty bad idea…

Steve

Another option would be to not use the tag as key.

sayan1

It’s not suitable for me, I have tried them before. Because I am use ID another sheets. For example register cow calves. On cow have multiple calves. Also vaccines.

sayan1