javascript - Prompt user for range in .gs function, pass array to html script and re-focus on the HTML dialog -
i have google sheet, want prompt user select ranges information from, store array, , create chart in html popup. have read bit google.script.run functionality, , understand without withsuccesshandler(htmlfunction).functiontocall() syntax @ end, html script moves onto next line. have .gs file below, , .html file, , was able graph work when entered static array in .gs function. however, seem struggling how return focus editor range, , bring html dialog box chart , right data function plots chart. saw here use google.script.host call editor.focus() function user can select cells, can't seem focus html popup without calling html file on again. here .gs function:
function retrievedata(){ var ss = spreadsheetapp.getactive(); var sheets = ss.getsheets(); var s = sheets[1]; var ui = spreadsheetapp.getui(); var response = ui.prompt("please enter first cell in category").getresponsetext(); var ir = s.getrange(response); var n= 0; var stored = []; stored.push(["income category", "frequency"]); while (ir.getvalue()!= "") { n = n +1; ir = ir.offset(1, 0); } ir = ir.offset(-n,0) for(i =0; i<n;i++) { stored.push([ir.getvalue(),ir.offset(n+2,0).getvalue()]); ir = ir.offset(1, 0); } return stored; } here html within body (stack overflow little strict, not going go through trouble of showing html; within body , communicating .gs file):
google.charts.load('current', {'packages':['corechart']}); google.charts.setonloadcallback(getdata); function getdata() { google.script.run.withsuccesshandler(drawchart).retrievedata(); google.script.host.editor.focus(); } function drawchart(stored) { //this apparently shows log of object //console.log(stored); var data = new google.visualization.arraytodatatable(stored); console.log(data); var options = {'title':'income', 'width':400, 'height':300, 'is3d':true}; // instantiate , draw our chart, passing in options. var chart = new google.visualization.piechart(document.getelementbyid('chart_div')); chart.draw(data, options); google.script.run.withsuccesshandler(drawchart).retrievedata(); } one last thing tried call spreadsheetapp.getui().showmodaldialog(html, "title") function 1 more time, without calling html file on again, , creating endless loop, don't seem have way that. idea how accomplish this?
here's simple example of picking range modeless dialog. few features thrown in measure.
code.gs:
function selrange()//run started. dialog displayed instructs select range. { var output=htmlservice.createhtmloutputfromfile('pickrange').setwidth(300).setheight(200).settitle('select range'); spreadsheetapp.getui().showmodelessdialog(output, 'range selector'); } function selcurrng() { var sso=spreadsheetapp.getactive(); var sh0=sso.getactivesheet(); var rg0=sh0.getactiverange(); var rng0a1=rg0.geta1notation(); rg0.setbackground('#777700'); return rng0a1; } function clrrange(range) { var sso=spreadsheetapp.getactive(); var sh0=sso.getactivesheet(); var rg0=sh0.getrange(range); rg0.setbackground('#ffffff'); } pickrange.html
<!doctype html> <html> <head> <base target="_top"> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script> <script> var grange=''; function selectrange() { $('#btn1').prop('disabled',true); $('#btn2').prop('disabled',false); google.script.run .withsuccesshandler(setresponse) .selcurrng(); } function setresponse(r) { grange=r; var msg='you have select range ' + r; $('#instr').css('display','none'); $('#rsp').text(msg); } function clearandclose() { google.script.run.clrrange(grange); google.script.host.close(); } console.log('my code'); </script> </head> <body> <div id="rsp"></div> <div id="instr">please select desired range.</div> <input type="button" id="btn1" value="range selected" onclick="selectrange();" /> <br /><input type="button" id="btn2" value="close" onclick="clearandclose();"; disabled="true" /> </body> </html> 
Comments
Post a Comment