Google Spreadsheet Script: Auto Iterate Next Row

Posted on 2011-10-13
Last Modified: 2012-08-13
I am a  newb when it comes to this stuff but I've been playing around Google App Script for a Google Spreadsheet and hoping someone can help/teach me how to customize code.

I found this code to populate data in my spreadsheet:
The spreadsheet I have is 6 columns wide (A-F) and what I want to do is customize the code so that as I enter information in a new row in column A, columns B-F will auto-populate.  I know I can just drag the formulas down, but it would be a "nice to have" to just have to auto populate.

I know Google App Script has a command called "getLastRow"  that can facilitate this problem, I'm just not sure how to set it up.

Thanks for the help.
Question by:Matthew Nguyen
    LVL 59

    Accepted Solution

    The specifics for getlastrow are:

    var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    // Use whatever you have established for the sheet already ... provided for clarity only

    var rw = sh.getLastRow() + 1;
    // getlastrow being the last populated row therefore plus 1 for the required row.

    LVL 59

    Expert Comment

    by:Chris Bottomley
    In the absence of any update from you, taking it a step further and showing for example a way of posting the formulae then:

    var sht = SpreadsheetApp.getActiveSheet();
      var rw = sht.getLastRow();
      var str
      sht.getRange(rw, 2).setValue (2);
      sht.getRange(rw, 3).setValue (3);
      str = 'B' + rw + ':C' + rw;
      sht.getRange(rw, 4).setFormula ('=sum(' + str + ')');
      str = 'B' + rw + ',C' + rw;
      sht.getRange(rw, 5).setFormula ('multiply(' + str + ')');

    Open in new window

    LVL 8

    Author Comment

    by:Matthew Nguyen
    Sorry, I was out all weekend, let me give this a go and see if I can get it to work, thanks for the help.

    LVL 8

    Author Closing Comment

    by:Matthew Nguyen

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: Create Mobile App Prototypes with Adobe XD

    This is a project-based course: we go through all the steps of creating a prototype from start to finish, using all the tools and features currently available in Adobe XD. You can complete the course in less than a day, plus all project files and fonts are included.

    Batch, VBS, and scripts in general are incredibly useful for repetitive tasks.  Some tasks can take a while to complete and it can be annoying to check back only to discover that your script finished 5 minutes ago.  Some scripts may complete nearly …
    Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
    Viewers will learn how to create a PivotTable and make basic changes to it in Excel 2013.
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now