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
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]);
}
Reference a range by using its A1 notation You can also explicitly specify the sheet by using the getSheetByName() method and then omit the name of the sheet from the range. The code below references the range A1:C6 in Sheet1. var range = SpreadsheetApp. getActive().
Apps Script is a good beginner coding language to learn if you work with Google tools, as it allows you to automate processes and do useful things without needing to learn everything about Apps Script first.
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
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With