I have an issue where I want to automate this process where some calculation is performed.

Link for the image: .png

I have an issue where I want to automate this process where some calculation is performed.

Link for the image: https://i.sstatic/9dxXRqKN.png

As a description of the table, I did an import range that would bring Column F which contains the formula and Column G which contains the price, I would like to automate it so that Column H would be the multiple of Column F and Column G. It so happens that Column F and Column G is taken from different sources.

Any attempt to trick it into thinking that it is a formula for example like =("="&F2)*G2 would lead to it saying:

"Function MULTIPLY parameter 1 expects number values. But Function MULTIPLY parameter 1 expects number values. But '=SUMIF('Sheet1'!A:A;'Sheet2'!$A$1;Sheet1'!B:B)' is a text and cannot be coerced to a number.

I've tried this but it doesn't seemed to work:

const sheet = SpreadsheetApp.getActiveSheet();
let formula = sheet.getRange(1, 5, sheet.getMaxRows(), 1).copyTo(sheet.getRange(1, 8));
target = sheet.getRange(1,8);
sheet.getRange(1,8).setFormula('='+ target);

Any help would be highly appreciated, thank you.

• google-sheets
• google-apps-script

• 3 Welcome to Stack Overflow. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results, making sure you're not describing an XY problem. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. – doubleunary Commented Mar 28 at 16:10

2 Answers 2

Use Array.map() and Range.setFormulas(), like this:

function setFormulas() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const formulas = sheet.getRange(1, 5, sheet.getLastRow(), 1)
    .getDisplayValues()
    .map(row => row.map(s => s ? '=' + s : null));
  sheet.getRange(1, 8, formulas.length, formulas[0].length).setFormulas(formulas);
}

See Array.map() and Range.setFormulas().

Alternatively, use an array formula with logic to decide which calculation to do in each row, avoiding scripting altogether.

You may also try this:

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const vl = sheet.getRange(2, 1, sheet.getLastRow() - 1, 8).getValues().filter(dt => dt[0] != "");
  const op = vl.map(r => [Number(r[5]) * Number(r[6])]);
  sheet.getRange(2, 8, op.length, op[0].length).setValues(op);
}

This gets the values in Column F and Column G, converts them into numbers, multiplies them, and outputs its result in Column H (returning #NUM! if any of the data is not a number). You can automate it by setting up a trigger to run the function automatically or manually do so with onOpen(e).

This should also work:

=ARRAYFORMULA(IF(F2:F*G2:G=0, "", F2:F*G2:G))

=MAP(F2:F, G2:G, LAMBDA(a, b, IF(a*b=0, "", a*b)))

However, if you're getting the error:

Function MULTIPLY parameter 1 expects number values. But Function MULTIPLY parameter 1 expects number values. But '=SUMIF('Sheet1'!A:A;'Sheet2'!$A$1;Sheet1'!B:B)' is a text and cannot be coerced to a number.

This means that the value in the cell of Column F is not a number. Check if the value of the cell is a number with TYPE or ISNUMBER. It should return 1: if value is a number or TRUE, respectively.

If it's returning a different output, wrap it with VALUE because it Converts a string in any of the date, time or number formats that Google Sheets understands into a number. The Sheet1'!B:B is also missing a ', it should be 'Sheet1'!B:B.

UPDATE

Since everything in Column F is literally the text for the formula without the = sign, you may use:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var vl = sheet.getRange(2, 6, sheet.getLastRow() - 1).getValues();
  var op = vl.map((r, i) => [`=${r}*G${i + 2}`]);
  sheet.getRange(2, 8, op.length, op[0].length).setValues(op);
}

This is an alternative in using setFormulas(formulas) that gets the text values of Column F, adds = before it, then adds *G# where # is the row number. The output of its result is a formula set in Column H.

REFERENCES

• Number
• Array.prototype.map()
• setValues(values)