CODEWITHSUNDEEP
javascriptgoogle-apps-script
Jake
Jake

Reputation: 15

Add row based on cell value - Google Script

I currently have this script that will add row beneath my "startRow". The code works but it is static; for the purpose of this example my row is 40 because I want a new row to be added under the contents I currently have in row 40. 

function addRows(){
  var startRow = 40;
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();

  sheet.insertRowsAfter(startRow , 1);
}

However, if I was to insert a row in my sheet above row 40, then the contents aforementioned will shift to row 41, and I want to insert a row under that, but being that this code is static it won't do that.

In a separate sheet, in cell A1, I am using the =row() function to pull in the number of that row that I want as this will be dynamic. If I insert a row above it, the number will change from 40 to 41, keeping the same example.

I want to know if there is a way I can transform this code to getValue of that cell, and insert the row under the row number that is provided in that cell, that way no matter how many rows are inserted/deleted above row 40, it will know exactly which row I want to insert my new row under.

For example, say I insert 3 rows above 40, I want this script to pull the value from my =row() cell (A1), which would be ("43"), and insert a new row under row 43.

Thanks in advance!

EDIT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

To give further context on why I'm doing this, I'm building a spreadsheet for proposals, each equipment section has its own data validated drop downs for products and sub-products.

Each section will have its own button on the right which will add an additional line item (row) underneath so more products can be added to each section while keeping the data validation drop down from the row above it. (I've only gotten through 3, which is when I realized my problem).

Spreadsheet screenshot

Each of them are running their own version of this function, the first at startRow = 40, the second at startRow = 43, the third at startRow = 46. When I test any of the scripts (buttons) individually they work like they're supposed to and add a row. But say I run the first script and then try the second script, it will still add a row after 43 meanwhile what I need is for it to add under 44 since everything got pushed down a row with the newly inserted row.

Here is what the spreadsheet looks like if I run only the second button. It is correct and will continue adding rows where it should since the startRow hasn't changed and is still 43.

Example running second button

But say I run the button one first and it inserts a row after row 40 and then I try the second button, it will still insert a row after 43 even though everything got pushed down a row and I need it to insert after 44 now in order to keep the data validation from the previous row.

Example running button 1 first then 2

I hope this clarifies the reason behind this project.

Upvotes: 1

Views: 2305

Answers (2)

St3ph
St3ph

Reputation: 2288

Answer updated with last element.

In fact you will have to specify a function for each button because you can't add a parameter when you use the function attached to a button.

Then you have another function that will search section based on name and find last celle with a value.

Code looks like :

function button1(){
  var label = "VIDEO EQUIPEMENT";// For each button specify a function with label to search
  addRow(label);
}

function addRow(label){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
//  var sheet = SpreadsheetApp.openById('SHEET_ID').getSheetByName('Sheet2')
  var data = sheet.getDataRange().getValues();
  for (var i = 0 ; i < data.length ; i++) {
    var line = data[i];
    if(line[0] == label){ // we use [0] as it is in column A
      var find = i ;
      break ;
    }
  }

  for (var i = find ; i < data.length ; i++) {
    var line = data[i];

    if(line[0] == ""){
       var lastLine = i ;
      break;
    }else{
      var lastLine = i+1 ;
    }
  }

  Logger.log(lastLine);

  // Then do What you want 
}

Stéphane

Upvotes: 0

ziganotschka
ziganotschka

Reputation: 26836

  • You are storing the row number dynamically with =row() in a separate spreadsheet
  • You want to retrieve this value dynamically and assign it to var startRow for insertRowsAfter()

This is how you can do it:

function addRows(){
 //provided the row number is store in a different spreadsheet in a sheet called "savedRowSheet", cell "A1":
  var rowSheet=SpreadsheetApp.openById(YOUR SPREADSHEET_ID').getSheetByName('savedRowSheet');
  var rowValue=rowSheet.getRange('A1').getValue(); 
  var startRow = rowValue;
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.insertRowsAfter(startRow , 1);
}

References:

Upvotes: 1

Related Questions