Google apps script AppendRow to the top

问题:

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

1. first, surname, name
2. second, surname, name
3. third, surname, name

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

so if another time in form will be

4. fourth, surname, name
5. fifth, surname, name
6. six, surname, name

data will be added like

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]);
}

回答1:

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
}