Search code examples
google-apps-scriptgoogle-sheetsnamed-ranges

Google Sheets Dialog with Dropdown and Insert Row into Named Range

I have a sheet that my company uses to keep track of open shifts within the company. I want the script to add rows into the proper named range on the proper sheet. The user will select an account from the drop down and put in a number of open shifts. The number of open shifts will be added as rows to the account via a Named Range. The code for "This Week" will insert rows into two separate sheets via a Named Range. (This Week, This Week_writeonce)

Here is the code I have so far,

function onOpen() {
 SpreadsheetApp
   .getUi()
   .createMenu("Add Holes")
   .addItem("Add Holes for This Week", "showThisWeekSidebar")
   .addItem("Add Holes for Next Week", "showNextWeekSidebar")
   .addToUi();
}

function showThisWeekSidebar() {
  var html = HtmlService
      .createTemplateFromFile('This Week');

  // Add the dropdown lists to the template
  html.namedRangesDPDWN = SpreadsheetApp.getActiveSheet().getRange("Named Ranges!NamedRanges").getValues();

  // Keep adding the variables you need based on the ranges containing your dropdown values
  // ...

  // Prepares the template to be shown in the UI
  html = html.evaluate()
      .setTitle('Company Name')
      .setWidth(200);

  SpreadsheetApp.getUi().showSidebar(html);
}

function showNextWeekSidebar() {
  var html = HtmlService
      .createTemplateFromFile('Next Week');

  // Add the dropdown lists to the template
  html.namedRangesDPDWN = SpreadsheetApp.getActiveSheet().getRange("Named Ranges!NamedRanges").getValues();

  // Keep adding the variables you need based on the ranges containing your dropdown values
  // ...

  // Prepares the template to be shown in the UI
  html = html.evaluate()
      .setTitle('Company Name')
      .setWidth(200);

  SpreadsheetApp.getUi().showSidebar(html);
}

Below is the HTML code.

<center>
<body bgcolor="#99a3a4">
  <font face="arial" color="black">
    <b>Add Holes for This Week</b><br><br>

    <!-- Create input fields to accept values from the user -->
    Account:<br>
    <select id="Named Ranges">
    <? for (let i in namedRangesDPDWN) { ?>
      <option value="<?=namedRangesDPDWN[i]?>"><?=namedRangesDPDWN[i]?></option>
    <? } ?>
    </select>
    <br><br>

 <div class="block form-group">
    <label for="numberOfHoles"># of Open Shifts</label><br>
    <input type='text' name='email' id="email" required="required"/>
    </div><br>

  <div>
    <button type="submit"id="submit-form">Submit</button>
  </div>

Trying to get the code to count the number of rows in named range, if greater than 2, do not account for existing rows.

// Take the Account and number of holes from
// the sidebar and insert rows to the proper named range
function insertRowNext(account,n_rows) {  
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var ws = ss.getSheetByName('Next Week');  // Change to your sheet name
    
    //Replace space with underscore
    account=account.replace(/ /g,"_");
    var nameRange = 'Next Week!'+account;
    var range = ss.getRangeByName(nameRange);
    var rangeRows = range.getNumRows();
    n_rows-=1;
    
    if(rangeRows == 2){
      //add row      
      ws.insertRowsBefore(range.getLastRow(),n_rows);
      //Show all rows in the namedRange
      ws.showRows(range.getRow(),range.getNumRows()+n_rows);
      
    }
    else if (rangeRows > 2) {
      //add row      
      ws.insertRowsBefore(range.getLastRow(),n_rows+1);
      //Show all rows in the namedRange
      ws.showRows(range.getRow(),range.getNumRows()+n_rows+1);
    }
 }

Here is the sample sheet. https://docs.google.com/spreadsheets/d/1zxdqkksdORfAUlmhnQmefKpxn8n-hB-3YeHdRiNLCLs/edit?usp=sharing

Solution

  • Here is a sample code: (For Add Holes This Week menu only)

    This Week.html:

    <center>
<body bgcolor="#99a3a4">
  <font face="arial" color="black">
    <b>Add Holes for This Week</b><br><br>

    <!-- Create input fields to accept values from the user -->
    Account:<br>
    <select id="Named Ranges">
    <? for (let i in namedRangesDPDWN) { ?>
      <option value="<?=namedRangesDPDWN[i]?>"><?=namedRangesDPDWN[i]?></option>
    <? } ?>
    </select>
    <br><br>

 <div class="block form-group">
    <label for="numberOfHoles"># of Open Shifts</label><br>
    <input type='text' name='email' id="email" required="required"/>
    </div><br>

  <div>
    <button type="submit"id="submit-form" onclick="processInput()">Submit</button>
  </div>
  <script>
    function processInput(){
      var account = document.getElementById("Named Ranges").value;
      var count = document.getElementById("email").value;

      google.script.run.withSuccessHandler(success).insertRowThis(account,count);
    }

    function success(){
      alert("Row added successfully");
    }
  </script>

    Modifications:

    • I added a script to call processInput() when submit button was clicked.
    • In processInput(),It will get the account selected and # of open shifts. Then pass them as an argument and call insertRowThis() in the server side.

    We used google.script.run.withSuccessHandler(function) to call server-side Apps Script function which will then call the client-side callback function to run when the server responds. In this example the client-side callback is success()

    addHolesMenu.gs

     function insertRowThis(account,n_rows) { 
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var ws = ss.getSheetByName('This Week');
    
    //Replace space with underscore
    account=account.replace(/ /g,"_");
    var nameRange = 'This Week!'+account;
    n_rows-=1;
    Logger.log(nameRange);
    Logger.log(n_rows);
    if(n_rows>0){
      //add row
      var range = ss.getRangeByName(nameRange);
      Logger.log(range.getRow());
      Logger.log(range.getLastRow());
      ws.insertRowsBefore(range.getLastRow(),n_rows);
      //Show all rows in the namedRange
      ws.showRows(range.getRow(),range.getNumRows()+n_rows);
    }
 }

    Modifications:

    • I added insertRowThis() which accepts 2 parameters
    • Since this menu is for adding rows in sheet This Week. I selected that particular sheet using Spreadsheet.getSheetByName(name).
    • Replace space with underscore in the account's name
    • Subtract 1 from the row input count
    • Create a nameRange string to get the specific range in sheet This Week. Then use Spreadsheet.getRangeByName(name)
    • Insert the desired number of rows before the end of the namedRange. The reason behind this is because, if we insert the row after the last row of the namedRange, it won't be included in the namedRange itself. To automatically adjust the namedRange when adding additional rows, it should be added before the last row of the namedRange.
    • Show all the rows under the namedRange including the newly added rows

    Output:

    enter image description here

    enter image description here

    enter image description here

    (Update)

    Add formula in column E:

    // ==========================================================================================================
// Take the Account and number of holes from
// the sidebar and insert rows to the proper named range
function insertRowThis(account,n_rows) {  
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var ws = ss.getSheetByName('This Week');  // Change to your sheet name
    
    //Replace space with underscore
    account=account.replace(/ /g,"_");
    var nameRange = 'This Week!'+account;
    var range = ss.getRangeByName(nameRange);
    var rangeRows = range.getNumRows();
    n_rows-=1;
    Logger.log(range.getA1Notation());
    Logger.log(rangeRows);
    Logger.log(range.getRow());
    Logger.log(range.getLastRow());
    if(rangeRows == 2){
      //add row      
      ws.insertRowsBefore(range.getLastRow(),n_rows);
      //Show all rows in the namedRange
      ws.showRows(range.getRow(),range.getNumRows()+n_rows);
      
    }
    else if (rangeRows > 2) {
      //revert subtracted row count
      n_rows+=1;
      //add row      
      ws.insertRowsBefore(range.getLastRow(),n_rows);
      //Show all rows in the namedRange
      ws.showRows(range.getRow(),range.getNumRows()+n_rows);
    }

    //Get formula in the header row in column E
    var formula = ws.getRange(range.getRow(),5).getFormula();
    Logger.log(formula);
    //Add formula in the newly added rows
    for (var i = 0; i<n_rows; i++){
      Logger.log("row: "+(range.getLastRow()+i));
      ws.getRange(range.getLastRow()+i,5).setFormula(formula);
    }
 }

    You can refer to this sample code to adjust your other menu. You can remove unnecessary logs, I just used them for debugging