Simple getValues/setValues function yields #ERROR-CodePudding

So I have this script:

function copyThis() {
  const sheetName1 = 'A'
  const sheetName2 = 'B'

  sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName1)
  values = sheet1.getRange('BJ8').getValues()
  
  sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName2)
  sheet2.getRange('B12').setValues(values)
}

The result is #ERROR! in cell B12 in sheet B. Please help.

Note: Cell BJ8 contains a formula. I want to copy its value to B12 which already contains a value.

I was expecting it to get the value (a number) in cell BJ8 from sheet A and paste it into cell B12 in sheet B.

CodePudding user response：

The problem is that your script is passing an Array to a cell. Replace

 values = sheet1.getRange('BJ8').getValues()

 values = sheet1.getRange('BJ8').getValue()

and

 sheet2.getRange('B12').setValues(values)

 sheet2.getRange('B12').setValue(values)

The above will pass a number, string, boolean or Date instead of an Array.

Resources

https://developers.google.com/apps-script/guides/sheets

CodePudding user response：

This works also:

function copyThis() {
  const ss = SpreadsheetApp.getActive();
  const sh0 = ss.getSheetByName("Sheet0")
  const sh1 = ss.getSheetByName("Sheet1");
  sh1.getRange("A2").setValues(sh0.getRange("A1").getDisplayValues())
}