5 Lines of Code to Automatically Add New Rows to Your Google Sheets Reports

Ever find yourself doing the same repetitive tasks in your Google sheets, day in and day out?

It’s likely that there are all kinds of ways you could automate your work, which in theory gives you time back for other work that can’t be performed by a script.

Here’s one small task that almost everyone does manually:

Adding a new row to the top of a spreadsheet tab

Many reports call for a new row to be added to the top of a spreadsheet each day. And often we’re content to …

  • Right-click row 2
  • Choose “Insert row above”
  • Do it again the next day

A better way

Use these 5 lines of code and you’ll never have to do that again:

function addNewRow() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getSheetByName("name_of_spreadsheet_tab");
  sheet.insertRowBefore(2);
}

That’s it! The script above will insert a row into position “2” on your spreadsheet tab whenever you run the script.

To use the code in a Google Sheet, click “Tools” > “Script editor…” inside your spreadsheet:

And then replace the sample code snippet in the editor with the script above. Be sure to replace name_of_spreadsheet_tab with the actual name of your sheet tab.

To set this script up to actually run each day, inside the code editor click “Resources” > “Current project’s triggers“.

Then, inside the popup that appears, add a “Day timer” trigger that runs on a schedule that you like and click “Save“. Here’s an example:

You can also click the blue “notifications” link to set notification preferences for when the script fails. (It happens every once in a while due to a Google server error).

As an example, I like to be emailed immediately when a script like this fails so that I can manually perform the task right away:

Extending the script

We can modify the script in several ways to further automate common tasks.

Insert yesterday’s date into cell A2

If we add a new row to the top of a spreadsheet tab to help automate the process of reporting on yesterday’s data, wouldn’t it be nice to automatically have yesterday’s date be filled in as well?

Not a problem – just a few more lines in our script:

function addNewRow() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getSheetByName("Sheet1");
  sheet.insertRowBefore(2);
  var d = new Date();
  d.setDate(d.getDate()-1);
  sheet.getRange("A2").setValue(d.getFullYear().toString().concat("-",d.getMonth()+1,"-",d.getDate()));
}

Here’s what the spreadsheet should look like after running that script:

If you prefer a different date format, you can use any of Google’s built-in date formatting functions via “Format” > “Number” from the top menu. Since the script is just populating cell A2 with the date value, we’re free to format it however we like and the underlying value will remain unchanged. No need to worry about handling formatting in the script code itself.

Insert last week’s start date into cell A2

Some reports are designed to show weekly data, and they are typically compiled after a week has completed. If you’d like to insert a new row with the start date (or end date) of the last week, try one of these changes:

Instead of d.setDate(d.getDate()-1);, use d.setDate(d.getDate()-7);.

If you start your weeks on Sundays, be sure to set your script to run on Sunday as well. Same for Mondays – if you start your week on Mondays, get last Monday’s date with the -7 modification, and set your script to run on Mondays.

Insert last month’s start date into cell A2

If you have a sheet that just needs a new row at the start of each month, try this:

var d = new Date();
d.setDate(d.getDate()-1);
sheet.getRange("A2").setValue(d.getFullYear().toString().concat("-",d.getMonth()+1,"-01"));

That will return YYYY-MM-01 in cell A2, and the year and month will match the start date of the previous month.

Remember to set this particular script to only run once per month on the 1st.

Leave a Reply

Your email address will not be published. Required fields are marked *