Applying an array formula to a cell using an app script

[Mister D]

Howdy, 

I am trying to pos the the below array formula to a specific cell via an appscript

=ARRAYFORMULA(IFERROR( VALUE(SUBSTITUTE(REGEXEXTRACT(TO_TEXT(B2:B); "\d+,\d+|\d+.\d+|\d+"); "."; ",")); ))

The closest I have been able to come up with is 

  function reset() {

  var keys = ['1jK3AEfVcLR-vk6tpAC0lw4A6OWWkQXXXXXXXXXXX']; // list of spreadsheet ids

  var formula = ['=ARRAYFORMULA(IFERROR( VALUE(SUBSTITUTE(REGEXEXTRACT(TO_TEXT(B2:B); ""\"d+,"\"d+|"\"d+."\"d+|"\"d+"); "."; ",")); ))']

  for (var i = 0; i < keys.length; i++) {

    var ss = SpreadsheetApp.openById(keys[i]);

    var sheet = ss.getSheetByName('Dataincoming');

    sheet.getRange('D2').setFormula(formula)

  }

}

However, what I actually get in the cell is =ARRAYFORMULA(IFERROR( VALUE(SUBSTITUTE(REGEXEXTRACT(TO_TEXT(B9:B); "d+,d+|d+.d+|d+"); "."; ",")); ))

Note all the \ have been removed. I've a wall and I am hoping someone would be able to point me in the right direction to retain the \ within the formula. 

Any assistance much appreciated. 

[xse]

Hey, backslashes need to be escaped

let formula = `=ARRAYFORMULA(IFERROR( VALUE(SUBSTITUTE(REGEXEXTRACT(TO_TEXT(B2:B); "\\d+,\\d+|\\d+.\\d+|\\d+"); "."; ",")); ))`;

Have a look at the "Escape character" section here.
Also it's worth noting that formula doesn't need to be an array, it's a single cell you're putting that formula into and setFormula expects a string not an array.

[Kim Nilsson]

Why the use of slanted quotation marks?

I see you use ` and not regular '.

[xse]

It's an habit, i'm used to making strings with template literals i find it cleaner when you have to put variables inside and of the 3 different type of quotation marks they're the one you're less likely to have to escape mid-string.
Doesn't matter at all in this case thought.

[Kim Nilsson]

Ok, so GAS accepts it as another (third) type of quotation?

Kim

[xse]

Totally yeah they're treated as strings with the ability to embed  placeholders instead of having to do string concatenation with a lot of + signs and so on.
here's a screenshot:

[Kim Nilsson]

Oh, that's great. I do use + concatenation in some scripts.

Kim

[Adam Morris]

It’s not a GAS feature, it’s really just JavaScript and the new V8 engine.

It’s incredibly useful.

On Sun, Apr 18, 2021 at 3:54 PM 'Kim Nilsson' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:

Ok, so GAS accepts it as another (third) type of quotation?

Kim

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CACK38a5fu9T2S3ZxB5ccPAgT8tcruxuVhrc8rH81Z3PUHFdurA%40mail.gmail.com.

--

http://classroomtechtools.com

[Bruce Mcpherson]

JavaScript introduced ‘templates literals’ to allow variable substitution with strings, and it became  available via v8

Here’s a write up

https://ramblings.mcpher.com/apps-script/apps-script-v8/template-literals/

On Sun, Apr 18, 2021 at 8:22 AM 'Kim Nilsson' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:

Why the use of slanted quotation marks?

I see you use ` and not regular '.

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/34a1e2be-1cb8-4fa3-a118-e80d1155fa12n%40googlegroups.com.