Search code examples
javascripthtmlgoogle-apps-scriptgoogle-sheetssidebar

Refresh or reload sidebar when google sheet cell edit

I have google sheet and data drop-down on Cell C4. and I made custom sidebar with several input fields. What I want is to refresh or reload sidebar when I choose data on Cell C4 through drop-down. Plz, tell me your great Idea!

+++

value= is from C4. when user change the value of C4, I want to refresh sidebar or input field automatically to update input field with current value of C4.

function showsidbar() {
  var html = HtmlService.createTemplateFromFile("main.html").evaluate();  //createHtmlOutputFromFile('Page')
 
   html.setTitle('입력창입니다!')
    //  .setWidth(300);
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .showSidebar(html);
}

function include(filename){
   return HtmlService.createHtmlOutputFromFile(filename).getContent();
}



function bb(){
  var mainsheet = "기본";
    var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = sheet.getSheetByName(mainsheet);
  var value = targetSheet.getRange("N5").getValue();
   return value;;
   }

function Name(){
  var mainsheet = "기본";
    var sheet = SpreadsheetApp.getActiveSpreadsheet();
   var targetSheet = sheet.getSheetByName(mainsheet);
  var value = targetSheet.getRange("C4").getValue();
   return value;;
   }
  <html>
  <head>
  </head> 
  <body>
  <div style=" padding:10px; background-color:#ccebff">
   <form name="frm">
    <center><B>학생 정보</B> </center>
   
       
    <b>이름 </b>
    <input type="text" name="Name" ID="reginame" size="6" value=<?=Name()?>>
    
     <b>학번</b>
    <input type="text" name="Number" iD="reginum" size="4" value=<?=bb()?>>
   
    
    
    </form>
    </div>
    </body>
    </html>

Solution
    • You want to refresh the sidebar using the edited value of "C4", when the dropdown list of cell "C4" is changed.
    • You want to achieve this by modifying your script.

    In this answer, I would like to propose to use the installable OnEdit event trigger.

    Modified script:

    When your script is modified, please add the following function to your script. And, please install the OnEdit event trigger to the function of inatalledOnEdit.

    function inatalledOnEdit(e) {
  if (e.range.getA1Notation() == "C4") {
    showsidbar();
  }
}
    • When the dropdown list of the cell "C4" is changed, the function of inatalledOnEdit is run by the installable OnEdit event trigger. And, showsidbar() is run, and then, the sidebar is reopened, and the new value is set by value=<?=Name()?>.

    Reference: