Multiplying numbers by certain number

问题:

I`m trying to multiply integer by 1.1765

https://docs.google.com/spreadsheets/d/1jo06fR0KrZXs6p82tjjuGmOVI0JsVFr3jsT_B5Efn6Y/edit?usp=sharing

Above is the sample data

So i`m trying to multiply pre-spend column to 1.1765

The Actual Spend will be Pre-spend * 1.1765

I tried the below code

function multiply(){
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1')
  var data = sheet.getDataRange().getValues();
  var newspend = sheet.getRange(1,1,data.length,1).getValues()*1.1765;
  var rng = sheet.getRange(2,2,sheet.getLastRow(), 1);
  Logger.log(newspend)
  rng.setValues(newspend)
}

But I get an error message saying,

Cannot find method setValues(number)

I think this is because things are not in array..?

How should we tackle this?

回答1:

Rather than try and insert a value in those cells, you can use setFormula to insert a formula.

This answer is probably only one of several possible approaches.

---

function applyformula(){
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var lastRow = sheet.getLastRow();
  Logger.log("DEBUG: the last row is "+lastRow);
  var dataRange = sheet.getRange(1,2,lastRow-1,2);
  Logger.log("the data range = "+dataRange.getA1Notation());
  var variable = 1.1765;
  sheet.getRange(2,2,lastRow-1,1).setFormula("=A2*"+variable)
}

---

Screenshot