Extract only a specific number from html table sell-CodePudding

I have this current lcode in my script which populates a cell in my sheet;

sheet.getRange(targetcell).setFormula('=INDEX(QUERY(IMPORTHTML("https://www.futwiz.com/en/consumables/positions","table",1),"select Col2"),G1)');

The output into my google sheet cell is;

"2,800

3 min ago"

What I would like to do is remove everything not needed, and leave ONLY the integer value 2800 (without a comma)

i.e instead of being

"2,800

3 min ago"

it should be

2800

Does anyone know the most elegant solution to edit my line of code to achieve this?

Thank you.

CodePudding user response：

Solve it all in the formula itself. So place this is the .setFormula()

=ARRAYFORMULA(
 VALUE(
  SUBSTITUTE(
   REGEXEXTRACT(
    QUERY(
     IMPORTHTML("https://www.futwiz.com/en/consumables/positions","table",1),
    "select Col2 where Col2 contains '*'"),
   "\*(.*?)\*"),
  ",","")
))

So like this:

sheet.getRange(targetcell).setFormula(`=ARRAYFORMULA(VALUE(SUBSTITUTE(REGEXEXTRACT(QUERY(
   IMPORTHTML("https://www.futwiz.com/en/consumables/positions","table",1),"select Col2 where Col2 contains '*'"),"\*(.*?)\*"),",","")))`)

CodePudding user response：

Try with

.setFormula(`=regexextract(INDEX(QUERY(IMPORTHTML("https://www.futwiz.com/en/consumables/positions","table",1),"select Col2"),G1),"[0-9,.] ")`)