google-sheetsgoogle-apps-scriptweb-applications

Make web app from google spreadsheet with GS


I have a google spreadsheet with some google script (code.gs) and sidebar with html buttons (index.html) , working with script. Please explain me if it possible how I can make a web app from all this? I want make a web app just for more simple interface on HTML.

code.gs

   function goalmod (){
  var ss = SpreadsheetApp.getActiveSheet();
  var formattedDate = Utilities.formatDate(new Date(),"GMT+5", "dd.MM.YY HH:mm");
  var row = ss.getActiveCell().getRow();
  var r = ss.getRange(row, 7, 1, 3);
  var rValues = r.getValues();
  r.setValues([["", formattedDate, rValues[0][2] +" / "+rValues[0][0]+" "+rValues[0][1]]]);
  ss.setActiveRange(ss.getRange(row, 7));
}


function technologi (){
var ss = SpreadsheetApp.getActiveSheet();
  var row = ss.getActiveCell().getRow();
  var cell = ss.getRange(row, 7);
  cell.setValue('Technologist');
}


function economists (){
var ss = SpreadsheetApp.getActiveSheet();
  var row = ss.getActiveCell().getRow();
  var cell = ss.getRange(row, 7);
  cell.setValue('Economists');
}


function countnum (){
var ss = SpreadsheetApp.getActiveSheet();
  var row = ss.getActiveCell().getRow();
  var cell = ss.getRange(row, 7);
  var nomerscheta = ss.getRange(row, 13).getValue();
  cell.setValue('Count'+" "+nomerscheta);
}


function ZPutechnologov (){
var ss = SpreadsheetApp.getActiveSheet();
  var row = ss.getActiveCell().getRow();
  var cell = ss.getRange(row, 7);
  var ZPnum = ss.getRange(row, 12).getValue();
  cell.setValue('ZP'+" "+ZPnum+'gave to technologist');
}


function goal1 ()
{
goalmod();
technologi();
}


function goal2 ()
{
goalmod();
economists();
}


function goal3 ()
{
goalmod();
countnum();
}


function goal0 ()
{
goalmod();
ZPutechnologov();
}

function newzakaz() {
   var sheet = SpreadsheetApp.getActiveSheet();
 var lastRow = sheet.getLastRow();
 var lastDateCell = sheet.getRange(lastRow+1, 10);
 var rangeToCopy = sheet.getRange(lastRow, 10);
 rangeToCopy.copyTo(lastDateCell);

  var city = sheet.getRange(lastRow+1, 4);
 var rangeToCopy = sheet.getRange(lastRow, 4);
 rangeToCopy.copyTo(city);

  var tel = sheet.getRange(lastRow+1, 5);
 var rangeToCopy = sheet.getRange(lastRow, 5);
 rangeToCopy.copyTo(tel);

  var mail = sheet.getRange(lastRow+1, 6);
 var rangeToCopy = sheet.getRange(lastRow, 6);
 rangeToCopy.copyTo(mail);

  var formattedDate = Utilities.formatDate(new Date(),"GMT+5", "dd.MM.YY HH:mm");
  var DateCell = sheet.getRange(lastRow+1, 8);
  DateCell.setValue(formattedDate);
  var CreateDate = sheet.getRange(lastRow+1, 18);
   CreateDate.setValue(formattedDate);


 var lastNumberCell = sheet.getRange(lastRow+1, 1);
  var nextnumber = sheet.getRange("B2").getValue();
  lastNumberCell.setValue(nextnumber);

  var FirstCell = sheet.getRange(lastRow+1, 2);
  sheet.setActiveRange(FirstCell);
}

function showSidebar() {

  var html = HtmlService.createHtmlOutputFromFile('index')
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle('My custom sidebar')
      .setWidth(50);
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .showSidebar(html);
}

index.html

    <style>
.but {
  color: black;
  border: 0px;
  position: relative;
  top: -8px;
  width: 284px;
  padding-top: 8px;
  padding-bottom: 8px;
     -webkit-transition: 0.8s ease-in-out;
} 

button:hover {
  cursor: pointer;
    background-color: rgb(106, 178, 242);
}

.goal0 {
  background-color: #FCE5CD;
}
.goal1 {
  background-color: #F9CB9C;
}
.goal2 {
  background-color: #FFE599;
}
.goal3 {
  background-color: #D9EAD3;
}
.goalmod {
  background-color: #93C47D;
}

.newzakaz {
  background-color: #87D37C;
    margin-bottom: 15.5px;
}



</style>
 <button type="button" class="but newzakaz" onclick="google.script.run.newzakaz()">Новый заказ</button>
  <button type="button" class="but goal0"  onclick="google.script.run.goal0()">ЗП у технологов</button>
    <button type="button" class="but goal1" onclick="google.script.run.goal1()">Технологи</button>
      <button type="button" class="but goal2" onclick="google.script.run.goal2()">Экономисты</button>
        <button type="button" class="but goal3" onclick="google.script.run.goal3()">Счет</button>
          <button type="button" class="but goalmod" onclick="google.script.run.goalmod()">В историю</button>

Solution

  • You actually don't need to create a new standalone script file, you can use the script file that is bound to your spreadsheet. You'll need two main changes, the first one is to access your spreadsheet by its ID since the sheet won't be "active" anymore and the second will be to replace your function showSidebar() with a new one called doGet().

    This doGet function is actually the conventional 'entry point' of all webapps, the function that will tell the server to start with.

    The code in this function should essentially be identical to the one you use in showSidebar() but instead of ending with

    SpreadsheetApp.getUi().showSidebar(html);
    

    it becomes simply

    return html;
    

    since your variable called html is actually the rendering of your html file.

    Then (and that will be the last thing to do) you have to save a version of your script and deploy it as a webapp. (see this post for details)