javascript - google apps script html select options loaded from spreadsheet data - Stack Overflow

admin管理员组

文章数量:1026989

New to google apps scripting, javascript, etc...

Trying to create a sidebar in a spreadsheet that contains an html select object that is populated from a column in one of the sheets.

I've tried using code that I found in this post, but the function to load the options doesn't appear to be executing when the html is loaded. Any idea what I'm doing wrong?

code in code.gs:

function onOpen(e) {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .createMenu('USD Conversion')
      .addItem('Convert Dollars to Foreign Currency', 'showSidebar')
      .addToUi();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var csvMenuEntries = [{name: "Load rates from CSV file", functionName: "importFromCSV"}];
  var addCountriesMenuEntries = [{name: "Add Countries", functionName: "addCountries"}];
  var conversionEntries = [{name: "Convert Dollars for Foreign Currency", functionName: "showSidebar"}];
}

/**
 * Opens a sidebar in the document containing the add-on's user interface.
 */
function showSidebar() {
  var template = HtmlService
      .createTemplateFromFile('Sidebar')

  var htmlOutput = template.evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle('USD Conversion')
      .setWidth(400);

  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .showSidebar(htmlOutput);
}

function getListOptions() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CountryCodes");
  var lastRow = sheet.getLastRow();  
  var myRange = sheet.getRange("P2:P"+lastRow); 
  var countries = myRange.getValues(); 

  return( countries );
}

code in Sidebar.html:

<div class="labels">
    <p>Destination Country:</p>
</div>

<div>
  <select name="optionList">
    <option>Loading...</option>    
  </select>

</div>

<script src="//ajax.googleapis/ajax/libs/jquery/1.10.1/jquery.min.js">
</script>
<script>
    // The code in this function runs when the page is loaded.
    $(function () {
        google.script.run.withSuccessHandler(buildOptionList)
            .getListOptions();
    });

    function buildOptionList(countries) {
        var list = $('#optionList');
        list.empty();
        for (var i = 0; i < countries.length; i++) {
            list.append(countries[i]);
        }
    }
</script>

New to google apps scripting, javascript, etc...

Trying to create a sidebar in a spreadsheet that contains an html select object that is populated from a column in one of the sheets.

I've tried using code that I found in this post, but the function to load the options doesn't appear to be executing when the html is loaded. Any idea what I'm doing wrong?

code in code.gs:

function onOpen(e) {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .createMenu('USD Conversion')
      .addItem('Convert Dollars to Foreign Currency', 'showSidebar')
      .addToUi();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var csvMenuEntries = [{name: "Load rates from CSV file", functionName: "importFromCSV"}];
  var addCountriesMenuEntries = [{name: "Add Countries", functionName: "addCountries"}];
  var conversionEntries = [{name: "Convert Dollars for Foreign Currency", functionName: "showSidebar"}];
}

/**
 * Opens a sidebar in the document containing the add-on's user interface.
 */
function showSidebar() {
  var template = HtmlService
      .createTemplateFromFile('Sidebar')

  var htmlOutput = template.evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle('USD Conversion')
      .setWidth(400);

  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .showSidebar(htmlOutput);
}

function getListOptions() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CountryCodes");
  var lastRow = sheet.getLastRow();  
  var myRange = sheet.getRange("P2:P"+lastRow); 
  var countries = myRange.getValues(); 

  return( countries );
}

code in Sidebar.html:

<div class="labels">
    <p>Destination Country:</p>
</div>

<div>
  <select name="optionList">
    <option>Loading...</option>    
  </select>

</div>

<script src="//ajax.googleapis./ajax/libs/jquery/1.10.1/jquery.min.js">
</script>
<script>
    // The code in this function runs when the page is loaded.
    $(function () {
        google.script.run.withSuccessHandler(buildOptionList)
            .getListOptions();
    });

    function buildOptionList(countries) {
        var list = $('#optionList');
        list.empty();
        for (var i = 0; i < countries.length; i++) {
            list.append(countries[i]);
        }
    }
</script>

• javascript
• jquery
• html
• google-apps-script

Share Improve this question Follow edited May 23, 2017 at 10:31 CommunityBot 111 silver badge asked Jul 30, 2015 at 12:51 SCOTT STAFFORDSCOTT STAFFORD 3311 silver badge33 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 4

To reference your select list in jQuery by id, the select element needs an id attribute <select id="optionList" name="optionList">. To add an option to an option list, you can use the following syntax: list.append(new Option(countries[i],countries[i])); where the fist value is the name and the second is the value.

You Google Script code looks good but you could add Logger.log(countries); right before your return statement to validate that you are returning the expected results. Within your Google Script editor, you can test a function by clicking Run -> (function name) than click View -> Logs to view the logs. It's best to test your Google Script code and HTML code separately before trying to use them together.

New to google apps scripting, javascript, etc...

Trying to create a sidebar in a spreadsheet that contains an html select object that is populated from a column in one of the sheets.

I've tried using code that I found in this post, but the function to load the options doesn't appear to be executing when the html is loaded. Any idea what I'm doing wrong?

code in code.gs:

function onOpen(e) {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .createMenu('USD Conversion')
      .addItem('Convert Dollars to Foreign Currency', 'showSidebar')
      .addToUi();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var csvMenuEntries = [{name: "Load rates from CSV file", functionName: "importFromCSV"}];
  var addCountriesMenuEntries = [{name: "Add Countries", functionName: "addCountries"}];
  var conversionEntries = [{name: "Convert Dollars for Foreign Currency", functionName: "showSidebar"}];
}

/**
 * Opens a sidebar in the document containing the add-on's user interface.
 */
function showSidebar() {
  var template = HtmlService
      .createTemplateFromFile('Sidebar')

  var htmlOutput = template.evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle('USD Conversion')
      .setWidth(400);

  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .showSidebar(htmlOutput);
}

function getListOptions() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CountryCodes");
  var lastRow = sheet.getLastRow();  
  var myRange = sheet.getRange("P2:P"+lastRow); 
  var countries = myRange.getValues(); 

  return( countries );
}

code in Sidebar.html:

<div class="labels">
    <p>Destination Country:</p>
</div>

<div>
  <select name="optionList">
    <option>Loading...</option>    
  </select>

</div>

<script src="//ajax.googleapis/ajax/libs/jquery/1.10.1/jquery.min.js">
</script>
<script>
    // The code in this function runs when the page is loaded.
    $(function () {
        google.script.run.withSuccessHandler(buildOptionList)
            .getListOptions();
    });

    function buildOptionList(countries) {
        var list = $('#optionList');
        list.empty();
        for (var i = 0; i < countries.length; i++) {
            list.append(countries[i]);
        }
    }
</script>

New to google apps scripting, javascript, etc...

Trying to create a sidebar in a spreadsheet that contains an html select object that is populated from a column in one of the sheets.

I've tried using code that I found in this post, but the function to load the options doesn't appear to be executing when the html is loaded. Any idea what I'm doing wrong?

code in code.gs:

function onOpen(e) {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .createMenu('USD Conversion')
      .addItem('Convert Dollars to Foreign Currency', 'showSidebar')
      .addToUi();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var csvMenuEntries = [{name: "Load rates from CSV file", functionName: "importFromCSV"}];
  var addCountriesMenuEntries = [{name: "Add Countries", functionName: "addCountries"}];
  var conversionEntries = [{name: "Convert Dollars for Foreign Currency", functionName: "showSidebar"}];
}

/**
 * Opens a sidebar in the document containing the add-on's user interface.
 */
function showSidebar() {
  var template = HtmlService
      .createTemplateFromFile('Sidebar')

  var htmlOutput = template.evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle('USD Conversion')
      .setWidth(400);

  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .showSidebar(htmlOutput);
}

function getListOptions() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CountryCodes");
  var lastRow = sheet.getLastRow();  
  var myRange = sheet.getRange("P2:P"+lastRow); 
  var countries = myRange.getValues(); 

  return( countries );
}

code in Sidebar.html:

<div class="labels">
    <p>Destination Country:</p>
</div>

<div>
  <select name="optionList">
    <option>Loading...</option>    
  </select>

</div>

<script src="//ajax.googleapis./ajax/libs/jquery/1.10.1/jquery.min.js">
</script>
<script>
    // The code in this function runs when the page is loaded.
    $(function () {
        google.script.run.withSuccessHandler(buildOptionList)
            .getListOptions();
    });

    function buildOptionList(countries) {
        var list = $('#optionList');
        list.empty();
        for (var i = 0; i < countries.length; i++) {
            list.append(countries[i]);
        }
    }
</script>

• javascript
• jquery
• html
• google-apps-script

Share Improve this question Follow edited May 23, 2017 at 10:31 CommunityBot 111 silver badge asked Jul 30, 2015 at 12:51 SCOTT STAFFORDSCOTT STAFFORD 3311 silver badge33 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 4

To reference your select list in jQuery by id, the select element needs an id attribute <select id="optionList" name="optionList">. To add an option to an option list, you can use the following syntax: list.append(new Option(countries[i],countries[i])); where the fist value is the name and the second is the value.

You Google Script code looks good but you could add Logger.log(countries); right before your return statement to validate that you are returning the expected results. Within your Google Script editor, you can test a function by clicking Run -> (function name) than click View -> Logs to view the logs. It's best to test your Google Script code and HTML code separately before trying to use them together.

本文标签： javascriptgoogle apps script html select options loaded from spreadsheet dataStack Overflow