Get #ERROR! and #NAME? in cell sheet when I use the .setFormula () in Google Apps Script

Nerea

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:

error setformula

name error setformula

Could someone tell me what the problem really is?

Thank you in advance

Kin Siang

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"),"")');
 
};

enter image description here

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Google Apps Script setFormula() in sheet cell encounters "#REF!" issue by using function doGet(e)

How to get Error Message from a Google sheet cell using Google Apps Script?

Google Apps Script setFormula function

I get a corrupted PDF when I use Apps Script to convert a sheet to pdf and send in email

OAuth error when exporting Sheet as XLS in Google Apps Script

Google apps script to duplicate script in a different folder giving type error when I try to access new sheet id ()

Google Sheets + Apps Script: How to delete a sheet by name based on the value of a cell

Unable to get the right in setformula in apps script

In Google Apps script I am trying to retrieve a value from a cell in a Google sheet but the value is undefined

#NeedHelp Apps script to send email when cell condition met in Google sheet

Stop Multi Line Cell from combining into paragraph when emailed from Google Sheet via Apps Script

get last update of google sheet in apps script

loop through a script when a sheet name begins with X google apps script

Upload Image in google sheet cell using google apps script?

Google Apps Script: update cell to file name

How to SetFormula() to Cell in row hidden by filter applied in google sheet?

Can I use Google Apps Script to make a Google Form display randomized text from a Google Sheet?

Apps Script Google Sheet Change Cell Colors With Sleep

Go to the cell that contains today's date in a Sheet with Google Apps Script

Can I use Google Apps Script to replace text across multiple tabs in a Google Sheet?

Google Apps Script - Trying to find a Google Sheet by name on Gmail

When get active current cell, clear content of another cell Google Apps Script

Getting Error: "sheet.indexOf is not a function" when creating dropdown in Google Apps Script

How can I use a For loop to loop tabs on sheet index numbers in Google Apps Script?

Read Google Sheet Exported JSON by column name in Apps Script

when I set the value of a cell with google script it gives me an error

How can I fix the range of a Google Sheet MATCH function when using a move rows apps script?

How do I get 5 rows from the end of my sheet in Google Sheets using Apps Script?

How can I get this to protect a sheet on scripting basis (Google Apps Script)?