Fixing the Empty First Row When Creating a New Sheet in Google Apps Script

When using Google Apps Script to copy data from one sheet to a log sheet, a common issue arises: the new sheet ends up with an empty first row. This typically happens when the data insertion starts from getLastRow() + 1, even when the sheet is brand new and completely empty.

Here’s how we fix it to ensure that data always starts at row 1 when creating a new log sheet.

✅ What the Code Does

• Adds a custom menu called “adv” to your Google Sheets UI.
• When triggered, it:
  • Copies the currently selected range from the active sheet.
  • Checks if a sheet called "log" exists—if not, it creates one.
  • Appends the copied data to the next available row.
  • Colors the newly added rows red for visibility.
  • Adds a simple log of where the data was inserted.

🛠️ The Fix

We modified the code to detect if the log sheet is new (i.e., empty), and start inserting data at row 1 instead of row 2.

---

✅ Updated Code Snippet

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('adv')
    .addItem('copy', 'copytolog')
    .addToUi();
}

function copytolog() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const range = ss.getSelection().getActiveRange();
  const data = range.getValues();

  let sheetLog = ss.getSheetByName('log');
  if (sheetLog == null) {
    sheetLog = ss.insertSheet();
    sheetLog.setName('log');
  }

  // Fix: Check if sheet is empty
  const lastRow = sheetLog.getLastRow();
  const startRow = lastRow === 0 ? 1 : lastRow + 1;

  const setRange = sheetLog.getRange(startRow, 1, data.length, data[0].length);
  setRange.setBackground('red');
  setRange.setValues(data);

  // Optional: Log insertion point
  sheetLog.appendRow([`Data inserted starting at row ${startRow}`]);
}

---

🧠 Why This Matters

Google Sheets interprets even an empty sheet as having one row sometimes, due to formatting or metadata. This fix ensures you only offset the row if there’s actual content, avoiding annoying blank lines at the top of your log sheet.

---

🚀 Try It Yourself

• Open any Google Sheet
• Add the script above via Extensions → Apps Script
• Reload the sheet
• Select some cells and choose adv → copy from the menu
• Watch the log sheet get created (without an empty first line!) and updated