Adding rows based on a few variables

Question

I'm looking for a function that can get inputs like - "project", "locale", "date" and the function will go to the relevant sheet in the spreadsheet based on the "project" value and will show the relevant rows based on the locale and date.

For example -
Spreadsheet X includes 3 sheets - Project 1, project 2 & summary. In the summary sheet, I have 3 variables - project, locale & date. I also have a button of "show". Once I'll click on the "show" the function will show me the data according the variables.

You can get started by digging through [Class SpreadsheetApp](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app). You gotta know how to use methods like open different sheets using [setActiveSheet(sheet)](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#setactivesheetsheet) to operate between sheets. With regard to Spreadsheet button, you can refer to this [SO post](https://stackoverflow.com/questions/28668766/adding-butt.ons-to-google-sheets-and-set-value-to-cells-on-clicking) — ReyAnthonyRenacia, Nov 12 '17 at 19:02

Cooper · Answer 1 · 2017-11-13T05:30:04.340

Here's a simple example that I think may be close to what you want. It includes a search button and a clear button and expects you to select a cell on the row of the summary table that you desire to see the data of. After selecting a row on the summary table you click the search button and the data from that project page for that location and date will be display on the side bar. If you wish to clear the data then press the clear button.

Code.gs:

function searchForDataSideBar()
{
   var ui=HtmlService.createHtmlOutputFromFile('searchinfordata');
   SpreadsheetApp.getUi().showSidebar(ui);
}

function searchForData()
{
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Summary');
  var cell=sh.getActiveCell();
  var row=cell.getRow();
  var cols=sh.getLastColumn();
  var rg=sh.getRange(row, 1, 1, cols);
  var vA=rg.getValues();
  var sh1=ss.getSheetByName(vA[0][0]);
  var rg1=sh1.getDataRange();
  var vA1=rg1.getValues();
  var s='';
  for(var i=1;i<vA1.length;i++)
  {
    var data=vA1[i];
    var a=vA[0][1];
    var b=data[0];
    var c=vA[0][2].valueOf();
    var d=data[1].valueOf();
    if(a==b && c==d)
    {
      s+=Utilities.formatString('Project Name:%s<br />Location:%s<br />Date:%s<br />Data1:%s<br />Data2:%s</br>Data3:%s</br>Data4:%s</br>Data5:%s</br><hr>',vA[0][0],data[0],Utilities.formatDate(new Date(data[1]), Session.getScriptTimeZone(), "E MMM dd,yy"),data[2],data[3],data[4],data[5],data[6])
    }
  }
  return s;
}

I used a sidebar to put the buttons on and to display your data. The name of my file was searchinfordata.html:

<!DOCTYPE html>
<html>
  <head>
   <title>Searching for Data</title>
   <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
   <script>
   function search()
   {
     google.script.run
         .withSuccessHandler(found)
         .searchForData();
   }
   function found(hl)
   {
     document.getElementById('found').innerHTML=hl;
   }
   function clearDiv()
   {
     document.getElementById('found').innerHTML='';
   }
   console.log('MyCode');
   </script>
  </head>
  <body>
    <div id="instr">Please Select a cell on the row in the summary tab that you wish to see the data for.</div>
    <input type="button" value="Search" onClick="search();" />
    <input type="button" value="Clear" onClick="clearDiv();" />
    <div id="found"></div>
  </body>
</html>

And here's the onOpen() function where you can build your menu or launch your sidebar.

function onOpen()
{
    SpreadsheetApp.getUi().createMenu('My Tools')
      .addItem('Show Sidebar','searchForDataSideBar')
    .addToUi();

    searchForDataSideBar();//launches your sidebar whenever you open your spreadsheet.
}

This is what the Summary Tab looks like:

P1 Tab:

P2 Tab:

P3 Tab:

And Finally the Sidebar:

Adding rows based on a few variables

1 Answers1