CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Maximilian Skigin
Maximilian Skigin

Reputation: 1

Find next free row depending on A in Google Script

I have the following question for Google sheets.

I use this script:

function copy() {
 
  var quelleTabellenName="Sheet1";

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(quelleTabellenName);
  var row = sheet.getActiveCell().getRow();
  var zielTabellenName="Sheet2";
  var zielTabelle=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(zielTabellenName);
  var letzteZeileVonZielTabelle=zielTabelle.getLastRow();
    zielTabelle.getRange((letzteZeileVonZielTabelle+1),1,1,11).setValues(sheet.getRange(row,1,1,11).getValues());
}

It copies the first 11 cells of the marked row from Sheet1 to the next free row in Sheet2, it works well.

Now my 2 questions:

  1. I don't like to mark the needed row every time, it's always the same, in that case row nr. 4, is it possible to change the active row to fix row 4, how to do it?

  2. This script only rewrites the columns 1-11 in Sheet2, it works well if column 12 and more are empty. I would like to make a endless formula in column 13 who sum the other columns, I would like to copy paste the formula down to row 999, but then he add a new row at row nr. 1000.

    Is it possible to change the script, that he only look for next free row in column A and take the next free? For example, there are 20 filled rows, with data in column 1-12 (A-L), in the lines 21+ only column L are filled with the formula, so the script should identify the row 21 as next free row and add the columns 1-11 (A-K) in that row but do not change the L21, is that possible? Now he write the data in row 1000+ ?

Thank you, sorry for my bad English

Upvotes: 0

Views: 462

Answers (2)

Maximilian Skigin
Maximilian Skigin

Reputation: 1

In Sheet2 i have 10 filled rows with Data in columns A-L. Columns A-K are filled by the script, in the column L I have a formula: =D*+E*.

This script copy the active row from Sheet1 and insert the first 11 columns in the first free row in Sheet2. If in Sheet2 there is no more data (in Column L), it works.

If I have 10 filled rows in Sheet2, it will fill the first 11 columns of row 11.

If I have in row 11 in L11 the formula =D11+E11, he thinks that row 11 is full and copies the 11 cells to row 12, I want that he look only for data in column A, so he would recognize that A11 is empty and copy the data to A11:K11, even if L11 is not empty.

I hope I could explain that

Upvotes: 0

JPV
JPV

Reputation: 27262

Replace this line

var letzteZeileVonZielTabelle=zielTabelle.getLastRow();

with

var letzteZeileVonZielTabelle= findFirstFreeRow(zielTabelle, 4)

and add this function to your project:

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

If you add a Logger.log(zielTabelle) you should see the row number in zielTabelle, which contains the first free cell (after all data) in column 4. Of course, you can use any column number that suits you.

I hope that helps?

Upvotes: 1

Related Questions