How to transfer data between Google Sheets using Apps Script

Other Questions Google Sheets
1

I’m working on a Google Apps Script that should move a single cell value from one sheet to another. The goal is to take data from cell location on my “Finance” sheet and place it in the first empty row of column E in my “Forecast” sheet.

However, I keep running into an error message saying the copyTo method doesn’t exist. I’ve been trying different approaches but can’t seem to get it working properly.

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

Can someone help me figure out what I’m doing wrong here? Thanks in advance!

2

yo, ur issue is that cellValue is a value and not a range obj, so it won’t have copyTo. instead, try targetSheet.getRange(nextEmptyRow, 5).setValue(cellValue). also, ur nextEmptyRow logic is off. use filteredData.length + 1 to get the right empty row.

3

Your issue lies in how the next empty row is determined and how data is transferred. When you use getValue(), it provides the actual value rather than a range object, so copyTo() will not work. Additionally, using indexOf('') on your filtered data is incorrect since it will not account for the correct index of empty cells. Instead, count the non-empty entries with filteredData.length + 1 to find the right row. Here’s the corrected approach:

function TransferData() {
  var sourceSheet = SpreadsheetApp.openById("your-id-here").getSheetByName("Finance");
  var cellValue = sourceSheet.getRange('H3').getValue();
  var targetSheet = SpreadsheetApp.openById("your-id-here").getSheetByName("Forecast");
  var columnE = targetSheet.getRange('E:E').getValues();
  var nextEmptyRow = columnE.filter(String).length + 1;
  targetSheet.getRange(nextEmptyRow, 5).setValue(cellValue);
}

This method directly sets the value into the target cell without attempting to copy from a non-range object.

4

You’re calling copyTo() on a primitive value instead of a Range object. getValue() returns the actual cell content, not a range with copy methods.

Your filtering logic has issues too - indexOf('') won’t work on the filtered array since you’ve already removed empty values.

Here’s a cleaner approach: use getValues() to grab all values from column E, then loop through until you hit an empty cell. Once you’ve got the target row number, just use setValue() to drop your data there. Way simpler than filtering and gives you better control over where the value goes.

5

You’re trying to use copyTo on a value, not a range object. Your row finding logic is also broken.

Honestly though, Apps Script for data transfers is way more complex than it needs to be. I’ve dealt with spreadsheet automation for years and debugging these scripts gets old fast.

I switched to Latenode for all my Google Sheets workflows. Set up a scenario that watches your Finance sheet changes and auto-copies values to the first empty row in Forecast. No code debugging, no range errors - just drag and drop.

The visual interface makes mapping data flow super easy. Error handling and logging are built in, so you actually know what broke when something goes wrong.

I built similar workflows for quarterly reporting and it saves hours monthly. Way more reliable than custom scripts that break whenever Google updates.

Check it out: https://latenode.com