How to use cell value instead of number-CodePudding

Im sorry I dont even know how to correctly phrase this question.

The line of code I need to edit is this;

sheet.getRange(targetcell).setValue('=INDEX(QUERY(IMPORTHTML("http://www.futbin.com/consumables/Position Change","table",2),"select Col2"),1)');

However instead of row 1, I want to use the value from cell G1. So, something like this;

sheet.getRange(targetcell).setValue('=INDEX(QUERY(IMPORTHTML("http://www.futbin.com/consumables/Position Change","table",2),"select Col2"),G1)');

However, this throws an error.

Could anyone advise how I can use the value in G1 instead of it being fixed?

Thank you.

CodePudding user response：

Try .setFormula instead of .setValue ...

sheet.getRange(targetcell).setFormula('=INDEX(QUERY(IMPORTHTML("http://www.futbin.com/consumables/Position Change","table",2),"select Col2"),G1)');

CodePudding user response：

If there were a column G from the table that results from that IMPORTHTML function then you would use this:

sheet.getRange(targetcell).setValue('=INDEX(QUERY(IMPORTHTML("http://www.futbin.com/consumables/Position Change","table",2),"select Col7"),1)');

Because G is column 7

However, there is only 3 columns from that IMPORTHTML. You can check this by entering the formular into a Google Sheet:

=IMPORTHTML("http://www.futbin.com/consumables/Position Change","table",2)

I suggest playing around with your formula in SHeets and once you've got what you want, go back into AppsScript.