Adding excel formula to appendRow

Question

I am new to Apps Script, but I have a code that fills form data in to a spreadsheet. It's working fine, but I want to make a hyperlink using the coordinates filled in.

This is the formula that I'm trying to add: =CONCATENATE("https://maps.google.com/?q=",formObject.lat,",",formObject.lng,")")

I want to add it to the bottom of this list.

sheet.appendRow([
  formObject.name,
  formObject.email,
  formObject.phone,
  formObject.comment,
  formObject.lng,
  formObject.lat,
  fileName,
  fileUrl,
  Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'")]);

How can I do that?

Answer 1

With appendRow, you can also assign formulas as contents.

So, you can concatenate the values on your script and add a HYPERLINK formula at the end of the list.

Sample code:

sheet.appendRow([
  formObject.name,
  formObject.email,
  formObject.phone,
  formObject.comment,
  formObject.lng,
  formObject.lat,
  fileName,
  fileUrl,
  Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'"),
  ('=HYPERLINK("https://maps.google.com/?q=' + formObject.lat + ',' + formObject.lng + '", "HYPERLINK LABEL")')]);