Question

151

Google apps script AppendRow to the top

rated 0 times [ 154] [ 3] / answers: 1 / hits: 21299 / 10 Years ago, tue, february 3, 2015, 12:00:00

As I know

appendRow(rowContents)
add values to the bottom of the spreadsheet
But how to add data from form in multiple rows in top of spreadsheet?

So if in form i have rows

first, surname, name

second, surname, name

third, surname, name

in sheet it must be placed as, and each time to the top

so if another time in form will be

fourth, surname, name

fifth, surname, name

six, surname, name

data will be added like

enter

Now I using this code, but it append all data to the end and works only with one row, i think i must loop throught all rows in form but how to do that?

function getValuesFromForm(form){

  var firstName = form.firstName,

      lastName = form.lastName,

      order = form.order,

      sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  sheet.appendRow([order,lastName,firstName]);

}

Answers

Only authorized users can answer the question. Please sign in first, or register a free account.

aidengiancarlop

Add To Favorites

Follow

Total Points: 234

Total Questions: 115

Total Answers: 94

Location: India

Member since Wed, Aug 26, 2020

4 Years ago

answered 10 Years ago billie · Accepted Answer

There's no single function to do this, but it's not that difficult write one. Here's an example:

function prependRow(sheet, rowData) {

  sheet.insertRowBefore(1).getRange(1, 1, 1, rowData.length).setValues([rowData]);

}

I would actually allow for an optional index, let's say we want to insert after the 2nd row, skipping the header.

function insertRow(sheet, rowData, optIndex) {

  var index = optIndex || 1;

  sheet.insertRowBefore(index).getRange(index, 1, 1, rowData.length).setValues([rowData]);

}

But appendRow also has another interesting characteristic, it's concurrent-safe. Which means it can be triggered multiple times in parallel and won't mess up. To make our function concurrent safe you have to lock it, like this:

function insertRow(sheet, rowData, optIndex) {

  var lock = LockService.getScriptLock();

  lock.waitLock(30000);

  try { 

    var index = optIndex || 1;

    sheet.insertRowBefore(index).getRange(index, 1, 1, rowData.length).setValues([rowData]);

    SpreadsheetApp.flush();

  } finally {

    lock.releaseLock();

  }

}

Then, to use it in your code just call..

function getValuesFromForm(form){

  //...

  insertRow(sheet, [order,lastName,firstName]); //could have passed an extra `2` parameter to skip a one line header

}