1

I have a daily work log for various customers whose appliances I repair. This work log takes the form of a Google Spreadsheet that has multiple sheets where each sheet is assigned to a specific, regular customer. I log the current date, equipment worked on and work done on the fly, by means of a basic web app via my mobile phone, which is based on Google Apps Script.

In my development environment, I use a modal dialog 'Test' pop-up box and it works perfectly. I select the customer from a drop-down list, enter information in the text fields and click 'save', and the relevant customer's sheet is populated with data. However, when I publish or deploy the app, only the left-most sheet (sheet1) gets populated, despite selecting any customer from the list. Somehow, accessing the 'server' spreadsheet via the URL makes the "google.script.run" calls ineffective. Does anyone have any ideas how I can solve this issue. Thanks in advance for any help in this regard.

**Index.html**

<!DOCTYPE html>
<html lang='en'>
<head>

<base target="_top">
  <meta charset="utf-8">
  <title></title>
  <meta name="viewport" content="width=device-width, initial-scale=1.0, 
   maximum-scale=1.0">    
</head> 
<body>
<form>  
<h1>Daily Work Record</h1>    

<p><label class="question" for="customer">Customer: </label>
            <select name="customer" id="customer" onchange    
="customerSelect(this)"/>

                <option></option>
                <option value="1">Customer A</option>
                <option value="2">Customer B</option>
                <option value="3">Customer C</option>
            </select></p>                   


<p><label class="question" for="equipment">Equipment: </label>       
             <select name="equipment" id="equipment"> 
                <option>Toaster</option>
                <option>Mixer</option>
                <option>Blender</option>
                <option>Slicer</option>                                             
                <option>Other</option>                              
              </select></p>                               

<p><label class="question" for="work">Work Done: </label>
<input type="text" name="work" id="work" placeholder="enter work carried 
 out" size="25" /></p>


 <input type="button" id="button" value="Save"
    onclick="google.script.run
       .withSuccessHandler(DataSaved)
        .itemAdd(this.parentNode)"/>             

</form> 
<script>    

    function customerSelect(menu){      
          if(menu.value=='1'){  
           google.script.run.sheetOne();   

        } else if(menu.value=='2'){
           google.script.run.sheetTwo();

        } else if(menu.value=='3'){
           google.script.run.sheetThree();             

        } else return false; 

        }   

</script>    

<div id="Message"></div>

<script>
       function DataSaved(){
       document.getElementById('Message').innerHTML="<span class='save'>Data 
       Saved</span>";
       return;
       }

</script>      
</body>    


**Code.gs**

function onOpen() {
   var ui = SpreadsheetApp.getUi();
   ui.createMenu('Test').addItem('PopUp', 'show').addToUi();
}

function show() {
   var html = HtmlService.createHtmlOutputFromFile('Index')
             .setSandboxMode(HtmlService.SandboxMode.IFRAME)  
             .setHeight(800)
             .setWidth(800)
   SpreadsheetApp.getUi().showModalDialog(html, 'Enter Job Details');  
}  

function doGet() {
    var html = HtmlService.createHtmlOutputFromFile('Index')
              .setSandboxMode(HtmlService.SandboxMode.IFRAME);
              return html;  
}

function sheetOne() {    
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Customer A");
    sheet.getRange("A1").activate();//used instead of setActiveSheet
    return true;
}

function sheetTwo() {    
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName("Customer B");
   sheet.getRange("A1").activate(); 
   return true;
}

function sheetThree() {    
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName("Customer C");
   sheet.getRange("A1").activate(); 
   return true;
}

function itemAdd(form) {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = SpreadsheetApp.getActiveSheet();
   sheet.appendRow([Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd"), 
    form.equipment, form.work]);
return true;
}
Improve this question

1 Answer 1

Reset to default
0

I would use a different strategy. I wouldn't try to pre-set the active sheet. So you don't need the customerSelect() function in the SELECT tag.

<select name="customer" id="customer" />

You also don't need any of the functions:

  • sheetOne
  • sheetTwo
  • sheetThree

Change the itemAdd() function to get the sheet tab by name

function itemAdd(form) {
   var ss = SpreadsheetApp.getActiveSpreadsheet();

   var sheetToValue = {//Object literal mapping user answer to sheet tab name
     "Customer A":"Customer A",
     "Customer B":"Customer B",
     "Customer C":"Customer C"
   }

   var whichSheetToGet = sheetToValue[form.customer];

   var sheet = ss.getSheetByName(whichSheetToGet);//Get sheet tab

   sheet.appendRow([Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd"), 
    form.equipment, form.work]);

  return true;
}
Improve this answer
1
  • 1
    Thank you. This solution works perfectly. And such minimal use of code!
    – TonyK
    Commented Nov 20, 2017 at 18:59

Not the answer you're looking for? Browse other questions tagged or ask your own question.