I'm trying to include a function in cells in a column, based on the value of the cell in the same row in the next column and the value in a fixed cell.
I have created my code in GAS that you can see here:
function enviar_datos_formulario_anadir_mascota_a_bd_fichas() {
var libro = SpreadsheetApp.getActiveSpreadsheet();
var hojaOrigen = libro.getSheetByName("Añadir ficha");
var hojaDestino = libro.getSheetByName("BD Fichas");
var row = hojaDestino.getLastRow() + 1;
var fechaHoy = hojaDestino.getRange("H6").setFormula("=TODAY()");
var fechaNacimiento = hojaDestino.getRange(row, 9);
var edad = hojaDestino.getRange(row, 8);
edad.setFormula('=IF(ISBLANK(\'fechaNacimiento\')=FALSE;DATEDIF(\'fechaNacimiento\';\'fechaHoy\';"Y");"")');
}
I have also tried with:
edad.setFormula('=IF(ISBLANK('+fechaNacimiento+')=FALSE;DATEDIF('+fechaNacimiento+';'+fechaHoy+';"Y");"")');
And also with:
edad.setFormula('=IF(ISBLANK(\"fechaNacimiento\")=FALSE;DATEDIF(\"fechaNacimiento\";\"fechaHoy\";"Y");"")');
For whatever reason, I can't get it to work. It throws me the #ERROR! or #NAME? notice. I attach screenshots below:
Could someone tell me what the problem really is?
Thank you in advance
You can use the following Apps Script
function to solve your problem, just replace the Sheet name
with your version then should be working, one the main problem of your existing code is did not obtain A1notation
and you do not need to perform escape
in the formula if you want to refer to other range
and variable hojaOrigen
was not used at all:
function UntitledMacro3() {
var spreadsheet = SpreadsheetApp.getActive();
var sheetD = spreadsheet.getSheetByName('Dockets');
var sheetU = spreadsheet.getSheetByName('USERNAMES');
var row = sheetU.getLastRow() + 1;
var fechaHoyrange = sheetU.getRange("H6");
fechaHoyrange.setFormula("=TODAY()");
var fechaHoy = fechaHoyrange.getA1Notation();
var fechaNacimiento = sheetU.getRange(row, 9).getA1Notation();
var edad = sheetU.getRange(row, 8);
edad.setFormula('=IF(ISBLANK('+fechaNacimiento+')=FALSE,DATEDIF('+fechaNacimiento+','+fechaHoy+',"Y"),"")');
};
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments