-1
I am creating a system that is integrated to Google Sheets, one of the spreadsheets I use has two pages, the Sending and the Code that is composed of the columns code and status, need that through a formula (need to be through formula, by limitations of an automation system I use) I can take from the Sending page, a code from the Code page and mark this code as "used" in the status column, also a check must be made to know which codes were used and get the next available. Below is a representation of the Code page.
Pagina Codigo
| code | status |
|---|---|
| code 1 | used |
| code 2 | used |
| code 3 | |
| code 4 |
In the example above you should take code 3 and put it in the same row in the status column. the code 3 would go to the Sending page from where the formula would be called in the message column, would look like this:
Pagina Envio
| message | sender |
|---|---|
| code 3 |
I tried to create a custom formula through Google’s Apps Script and was able to verify that the code was available, but I was unable to add the status. Below is the code I used
function getCode(pagin) {
var plan = SpreadsheetApp.getActiveSpreadsheet();
var guia = plan.getSheetByName(pagin);
var linha = 1;
while(guia.getRange(linha, 2).isBlank() == false){
linha = linha + 1;
}
guia.getRange(linha, 2).setValue("usado");
var codigo = guia.getRange(linha,1).getValue();
return codigo;
}
Where "pagin" is the name of the page. The problem is that when I try to use this script by writing to a cell =getCode("pagina1"), for example, it gives an error and says I’m not allowed to call setValue, searching a little I found several post saying that this is a limitation of Google Apps Script but I found nothing that could help me solve otherwise since I need to do this flow through a formula in the cell, If anyone knows a way to fix it I appreciate.