Simple getValues/setValues function yields #ERROR

Question

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.

Wicket · Accepted Answer · 2023-01-12 22:30:43Z

1

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

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

by

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

and

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

by

 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

answered Jan 12, 2023 at 22:30

Wicket

39.4k9 gold badges81 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

steam1901 Over a year ago

Thank you Ruben :) I can hardly believe that "s" at the end of getValues matters :P

Cooper · Accepted Answer · 2023-01-12 23:03:06Z

0

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())
}

answered Jan 12, 2023 at 23:03

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Collectives™ on Stack Overflow

Simple getValues/setValues function yields #ERROR

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related