CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Michael Krapf
Michael Krapf

Reputation: 75

Insert Data in first empty row

Hello Guys
I have a Sheets that has multipe separte tables on 1 page. Here is the Page.

enter image description here

Now i want to get the last row of one of those groups.
I used this Code to write text in the last row of a sheets where the is only 1 Table on 1 Page. 

opt2.getRange(opt2.getLastRow()+1, 2, 1, 1).setValue(keyword);
opt2.getRange(opt2.getLastRow()+0, 3, 1, 1).setValue(volume);

opt2 is the name of the Page
i tried setting the range to a new Value. 

var tab1 = opt2.getRange("B:D");
opt2.getRange(tab1.getLastRow()+1, 2, 1, 1).setValue(keyword);
opt2.getRange(tab1.getLastRow()+0, 3, 1, 1).setValue(volume);

But this also didnt work any ideas on how to do it ?
Thx for the help

Upvotes: 0

Views: 290

Answers (1)

JPV
JPV

Reputation: 27302

Instead of using .getLastRow() you can check the first free cell in a column by creating a helper function, that takes a sheetobject and a column number as parameters.

function findFirstFreeRow(sheet, colNum) {
    var v = sheet.getRange(1, colNum, sheet.getLastRow()).getValues(),
        l = v.length,
        r;
    while (l > 0) {
        if (v[l] && v[l][0].toString().length > 0) {
            r = (l + 2);
            break;
        } else {
            l--;
        }
    }
    return r;
}

Then, in your code call the function to compute the row number that has the first free cell in that column.

opt2.getRange(findFirstFreeRow(opt2, 2), 2, 1, 1).setValue(keyword);
opt2.getRange(findFirstFreeRow(opt2, 3), 3, 1, 1).setValue(volume);

Note: when there are no 'gaps' inside the column you can also try

var lastRow = opt2.getRange("B:B").getValues().filter(String).length + 1;

Upvotes: 2

Related Questions