Google Sheets script error when transferring data between sheets

Other Questions Google Sheets
1

I’m working on a Google Apps Script that should move a single value from one sheet to another. The goal is to grab data from cell ‘H3’ in my ‘Summary’ sheet and put it in the first empty row of column E in my ‘Forecast’ sheet.

However, I keep running into an error that says “TypeError: cellValue.copyTo is not a function”. I’ve tried different approaches but can’t seem to fix this issue. Any suggestions would be really helpful.

function TransferData() {
  var sourceSheet = SpreadsheetApp.openById("spreadsheet-id").getSheetByName("Summary");
  var cellValue = sourceSheet.getRange('H3').getValue();
  var targetSheet = SpreadsheetApp.openById("spreadsheet-id").getSheetByName("Forecast");
  var columnE = targetSheet.getRange('E:E').getValues();
  var filteredE = columnE.filter(function (item) {return item[0];});
  var nextEmptyRow = filteredE.indexOf('')+1;
  cellValue.copyTo(nextEmptyRow, {contentsOnly: true});
}
2

The problem is you’re trying to use copyTo() on a value from getValue(), not on a range. You need to either work with a range directly or use setValue() to move the data.

Also, your method for finding the next empty row is broken because you’re using indexOf(‘’) wrong on your filtered array. Here’s a better approach:

function TransferData() {
  var sourceSheet = SpreadsheetApp.openById("spreadsheet-id").getSheetByName("Summary");
  var cellValue = sourceSheet.getRange('H3').getValue();
  var targetSheet = SpreadsheetApp.openById("spreadsheet-id").getSheetByName("Forecast");
  var lastRow = targetSheet.getLastRow();
  var nextRow = lastRow + 1;
  targetSheet.getRange(nextRow, 5).setValue(cellValue);
}

This finds the last used row, calculates the next available row, and uses setValue() to add your data. Should fix your copyTo() issue.

3

You’re mixing up methods - getValue() returns the actual data, not a range object, so copyTo() won’t work on it. copyTo() only works on Range objects. If you want to stick with copyTo(), work with the source range directly instead of extracting the value first:

function TransferData() {
  var sourceSheet = SpreadsheetApp.openById("spreadsheet-id").getSheetByName("Summary");
  var sourceRange = sourceSheet.getRange('H3');
  var targetSheet = SpreadsheetApp.openById("spreadsheet-id").getSheetByName("Forecast");
  var lastRow = targetSheet.getLastRow();
  var targetRange = targetSheet.getRange(lastRow + 1, 5);
  sourceRange.copyTo(targetRange, {contentsOnly: true});
}

This keeps the range object intact throughout and lets you use copyTo() properly. The contentsOnly option copies just the value without formatting.

4

yep, that copyTo error is due to getValue() giving you raw data, not a range. also, your indexOf method is kinda off. just use setValue for a quick fix: targetSheet.getRange(targetSheet.getLastRow() + 1, 5).setValue(cellValue);. easy peasy!