Google Sheets: How to automate appending rows

• What this does: Append rows from one Google sheet to another & schedule the task to run daily
• Requirements: Baseline understanding of Google sheets
• Concepts covered: Google Apps Scripts, Google Sheets

One of the coolest features of Google Sheets is the ability to automate tasks via scripts.

For example, say you need to perform a daily or weekly task where you add new rows to an existing sheet. With Google Apps Scripts, you can quickly build automation into your Google Sheets – or other Google products – to complete and schedule routine tasks like this.

I ran into this situation where I needed a custom analytics report using another helpful Google Sheets tool – the Google Analytics add on. There was an existing spreadsheet and every day I had another sheet that refreshed with new data from Google Analytics. I needed the new data to be added as new rows to the existing spreadsheet.

Getting started

Create your spreadsheet in Google sheets or open your existing sheet. Sheet1 should contain the baseline data and Sheet2 will contain the new data that you will be appending.

Go to Tools -> Script Editor.

This will create a new untitled project. Click the project name to rename it.

In the code editor below, we’ll paste the script below. This is based on a script here but with some modifications.

function addRows() {
  var sApp = SpreadsheetApp.getActiveSpreadsheet();
  var s1= sApp.getSheetByName("Sheet1");
  var s2= sApp.getSheetByName("Sheet2");

  var s1values = s1.getRange(1,1,s1.getLastRow(),2).getValues();
  var s2values = s2.getRange(1,1,s2.getLastRow(),2).getValues();

  var lastRow = s1.getLastRow();
  s1.getRange(lastRow + 1, 1, s2values.length,2).setValues(s2values);
}

Let’s break this apart.

First, we’re creating a new function called addRows. Then we’re declaring 3 variables – the active spreadsheet, and then the two sheets. If your sheets are not named Sheet1 and Sheet2, simply change the names in quotes.

Next, we’re declaring variables that contain the actual data. We’ll first define a range and then use the getValues() method to grab the data.

In the getRange() method, you’ll see four parameters that define the data range.

• The first (1) is the starting row
• The second (1) is the starting column
• The third, s1.getLastRow() or s2.getLastRow(), as the name implies, gets the index value of the last row of data (basically counting the rows)
• The fourth (2) is how many columns you want to select

Finally, we’ll use setValues() to append the new rows from Sheet2 to Sheet1. The last lines of code will locate the last row in Sheet1 and then add 1 row. Beginning at the first blank row, it will set the values of the blank rows with the contents of Sheet2.

Removing duplicates

Depending on your end goal, you may also need to remove duplicate rows.

Luckily, Google has a tutorial and script already built for this. We’ll need to make a couple modifications to work with the existing variables we defined, so here is the full code below if you do need to automate appending rows then removing duplicates.

function addRows() {
  //Append rows
  var sApp = SpreadsheetApp.getActiveSpreadsheet();
  var s1= sApp.getSheetByName("Publish Date Ref");
  var s2= sApp.getSheetByName("Publish Date");
  var s1values = s1.getRange(1,1,s1.getLastRow(),2).getValues();
  var s2values = s2.getRange(1,1,s2.getLastRow(),2).getValues();
  var lastRow = s1.getLastRow();
  s1.getRange(lastRow + 1, 1, s2values.length,2).setValues(s2values);

  //Remove duplicates
  var data = s1.getDataRange().getValues();
  var newData = [];
  for (var i in data) {
    var row = data[i];
    var duplicate = false;
    for (var j in newData) {
      if (row.join() == newData[j].join()) {
        duplicate = true;
      }
    }
    if (!duplicate) {
      newData.push(row);
    }
  }
  s1.clearContents();
  s1.getRange(1, 1, newData.length, newData[0].length).setValues(newData); 
}

Automating

When we automate, the function name (addRows) will be important later.

For clarity’s sake, you may also want to rename the script, especially if you will be running multiple scripts. By default, it’s named code.gs, but you can click on the menu and select rename to call it something more specific.

Before we automate and deploy, we should also test our script. You can do this by simply clicking the Run button in your code editor.

Now the fun part – scheduling our task to run on its own.

On the script editor, select Triggers from the left hand menu (alarm clock icon).

Select Add Trigger.

A new dialog appears.

• Choose the function (the addRows function we just created).
• Under Select Event Source, select Time Driven. Select Day Timer, Week Timer, or whatever frequency you need the script to run.
• Set the day or time you want the script to run.
• Click Save.
• If it hasn’t already, Google will prompt you to authorize your App Script to access Sheets. Grant the permission.

On your trigger, make sure that failure notifications are enabled (they will be by default). If the script fails for some reason, you’ll get an email alert.

For more

Google Apps Scripts provides pre-built solutions for automating tasks similar to Microsoft Flow (for those who use an Office environment). So before you spend time Googling a solution for automation, you may want to check out what’s already available.