HOWTO: Automatically add a row to a spreadsheet in Google Docs

Have you ever found that you had to update an excel spreadsheet with the same information every month or every week? Are you using Google Docs for that spreadsheet? If you are, you are in luck since this tutorial will show you exactly how to do that.

First, you'll need to have the following squared away:

  • A Google account. So that you could access Google Docs
  • A spreadsheet that will hold the information and will get auto-populated on regular basis
  • Now that you got all the "pre-requisites" squared away, let's actually get to the good part!

    Why would I even need this?

    There are several applications for this requirement. Couple that come to mind are:

  • Paying off an off-the-books debt - If you are using bill pay and don't have a specific limit set up, you can track your payments and remaining balance using this approach
  • Monitoring your vacation hours - If you work for a company and you accrue some set amount of hours every week, this will help you keep track of where you are each week.
  • Still interested in this idea? Good, then you should read on! :)

    How this works

    The concept is pretty basic. You will have a little JavaScript script that will get executed every day at a certain time. This script is going to compare today's date to the starting date and determine if your spreadsheet needs to get updated. It is that simple!

    Scenario

    Let's consider writing a spreadsheet for tracking your vacation time accrual. We are going to have 4 columns like so:
    A1 = Date - Date when new entry has been entered
    B1 = Hours accrued - How many hours have accrued this period
    C1 = Available Hours - Total number of hours.
    D1 = Accrual Rate - How many hours we get each week

    The code

    Our code will have 3 major tasks:

  • Determining whether today is the day to add a new entry
  • Getting accrual rate (column D)
  • Adding new entry
  • Determining if new entry needs to be added

    To determine if spreadsheet update is needed, we're going to convert the initial date and today's date to milliseconds

    var startDate = new Date(2011, 1, 1);
    var today = new Date();
    // strip off the time.
    today = new Date(today.getYear(), today.getMonth(), today.getDate(), 0);
     
    var startDateInMs = startDate.getTime();
    var todayInMs = today.getTime();

    Then, we're going to get the time difference between today's date and starting date like so:

    var diff = Math.abs(todayInMs - startDateInMs);

    Lastly, we're going to determine whether or not the difference equates to any number of weeks. This can be done like so:

    // define how frequently we want to update
    var weeksBetween = 2;
     
    // get number of milliseconds in 1 week
    var oneWeekInMs = 7 * 24 * 60 * 60 * 1000;
     
    // determine whether or not it is time to update
    var isToday = (diff % (oneWeekInMs * weeksBetween ) == 0) ? true : false;

    Get accrual rate from the spreadsheet

    Since we are defining the accrual rate in our spreadsheet, we need our code to go and get it.
    First, we need to get our spreadsheet's ID. Here are a few simple steps:
    1) navigate to http://docs.google.com. This should bring you to your document management window
    2) Open the spreadsheet you want to be modified
    3) Your url will look something like this: https://spreadsheets.google.com/spreadsheet/ccc?key=abc1234&hl=en_US#gid=7. Your ID is everything after key= and either up to the & or, if & is not found, until end of the line.

    Now that we have the document ID, it is time get a reference to this document from the script. Here's how:

    var spreadSheet = SpreadsheetApp.openById("your id from step 3 above");
    SpreadsheetApp.setActiveSpreadsheet(spreadSheet);
    var sheet = spreadSheet.getSheetByName("name of the sheet you want to modify");

    Then, we're going to extract a value from a cell 'D2'.

    var cell = sheet.getRange("D2");
    var hoursPerPeriod = cell.getValue();

    Adding a new row

    Adding a new row is just like getting a cell and it's value, except that we're going to set a value. So, if you want to fill out cells A2, B2, C2 with values 1, 2, 3, your code would look something like this:

    var a2 = sheet.getRange("A2");
    var b2 = sheet.getRange("B2");
    var c2 = sheet.getRange("C2");
    a2.setValue("1");
    b2.setValue("2");
    c2.setValue("3");

    Of course, if you want to combine the total of all hours accrue to date, you will need get a value from the last row and add new accrual to it.

    var lastRowIndex = sheet.getLastRow();
    var tot = sheet.getValue("C" + lastRowIndex).getValue();

    Add a trigger so that your script would execute every day

    In order for your script to run every day, you need to set up a trigger (or an event) which is time based. To do that, just follow these simple steps:
    1) From your script editor window, click on Triggers | Current script's triggers...
    2) Click Add a new trigger button
    3) Fill out requested information.
    4) Click Save and you're done!

    Complete code segment

    Now that all the parts have been explained, let's look at the whole picture!
    Please note, that in order for you to start writing the code, you'll need to get into the code editor view. To get there simply navigate to Tools -> Scripts -> Script Editor.

    function updateSpreadsheet() {
      // get number of milliseconds in 1 week
      var oneWeekInMs = 7 * 24 * 60 * 60 * 1000;
     
      // define how frequently we're going to update
      var weeksBetween = 2;
     
      // set the starting date
      var startDate = new Date(2011, 1, 1, 0);
     
      // open up a spreadsheet and get a reference to it
      var spreadSheet = SpreadsheetApp.openById("abc123");
      SpreadsheetApp.setActiveSpreadsheet(spreadSheet);
      var sheet = spreadSheet.getSheetByName("Sheet1");
     
      // get today's date
      var today = new Date();
      today = new Date(today.getYear(), today.getMonth(), today.getDate(), 0);
     
      // convert both dates to milliseconds
      var startDateInMs = startDate.getTime();
      var todayInMs = today.getTime();
     
      // get the difference between the dates
      var diff = Math.abs(todayInMs - startDateInMs);
     
      // determine whether or not it is time to update
      var isToday = (diff % (oneWeekInMs * weeksBetween ) == 0) ? true : false;
     
      // if it is not the time to update, exit out of the routine
      if (!isToday) {
        return;
      }
     
      // get the accrual rate
      var cell = sheet.getRange("D2");
      var hoursPerPeriod = cell.getValue();
     
      // find index of last row.
      var lastRowIndex = sheet.getLastRow();
     
      // get current total hours
      var curTot = sheet.getRange("C" + lastRowIndex).getValue();
     
      // increment row index by 1 to point to a new row
      lastRowIndex++;
     
      // convert today's date to string
      var todayStr = (today.getMonth() + 1) + "/" + today.getDate() + "/" + today.getYear();
     
      // add a new row
      // fill in date
      sheet.getRange("A" + lastRowIndex).setValue(todayStr);
      // fill in hours accrued
      sheet.getRange("B" + lastRowIndex).setValue(hoursPerPeriod);
      // fill in total hours accrued
      sheet.getRange("C" + lastRowIndex).setValue(curTot + hoursPerPeriod);
    }

    Questions and/or comments are always welcomed!

    Comments