How to overwrite data in Google Sheets instead of adding new rows

Other Questions Google Sheets
1

I have a script that copies information from one sheet to another, but it keeps adding more rows each time I run it. I want to change this so it replaces the existing data instead of creating new rows.

Right now my code takes the last row from the first sheet and puts it at the bottom of the second sheet. This makes the second sheet grow bigger every time. What I need is for it to always overwrite the same row in the second sheet, so there is only one row of data at any time.

Can someone help me modify this to replace data instead of appending it?

function copySheetData() {
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DataSource');
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DataTarget');
  var lastRow = sourceSheet.getLastRow();
  var lastCol = sourceSheet.getLastColumn();
  var sourceData = sourceSheet.getRange(2,1,lastRow-2,lastCol).getDisplayValues();
  var processedData = sourceData.map(row => {
    return [row[0], row[2], row[4], row[6], row[8], row[10], row[12], row[14], row[16], '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''];
  })
  targetSheet.getRange(3,1,lastRow-2,37).setValues(processedData);
}
2

Clear the entire target sheet first with targetSheet.clear(), then write your data. Put it right before the setValues line. This way you start fresh each time instead of stacking rows on old data.

3

Your problem is in the range calculation. You’re using lastRow-2, so if your source has 10 rows, you’re writing 8 rows to the target every time. Don’t clear data each time - fix your range calculation instead. If you want to overwrite the same data consistently, figure out exactly how many rows you need in the target and use that fixed number. Like if you always want to replace rows 3-10: targetSheet.getRange(3,1,8,37).setValues(processedData.slice(0,8)); Or track what you wrote before by storing the row count from last time, then clear that exact range before writing new data. This stops the accumulation issue. The key is being explicit about your target range dimensions instead of letting them change based on source data size.

4

Your code keeps adding rows because you’re writing all the source data every time you run it. The script doesn’t know to replace the old data - it just keeps appending.

Quick fix: Clear the target range before writing new data. Add this line right before setValues(): targetSheet.getRange(3, 1, targetSheet.getLastRow(), 37).clearContent();

Or if you only want one row in the target sheet, use a fixed range instead: targetSheet.getRange(3, 1, 1, 37).setValues([processedData[0]]); This grabs just the first processed row.

The main thing is controlling exactly where you’re writing instead of letting the range expand based on your source data.