Solved: How to pass params to HYPERLINK formula - Page 2

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