LANG SELRCT

Apps Script Reference  (Create: Create new Spreadsheet | Create new Apps Script

Saturday, April 13, 2019

シートのデータをdatalistに読み込みたい


スプレッドシートにあるこのようなセルのデータを
nameemail
いちごichigo@〜.com
りんごringo@〜.com
みかんmikan@〜.com


getValuesで取得して
 [[name, email], [いちご, ichigo@〜.com], [りんご, ringo@〜.com], [みかん, mikan@〜.com]]


HtmlServiceでこのように読み込みたくて書いたコードです。


valueの文字列の右に、textContentの文字列も表示できた



コード.gs
function doGet(){
 return HtmlService.createHtmlOutputFromFile('index'); 
}

function returnData() {
  var values = returnValues();
  var objs = createObjs(values);
  return objs;
}

function returnValues() {
  var ssUrl = 'SPREADSHEET_URL';
  var ss = SpreadsheetApp.openByUrl(ssUrl);
  var sheet = ss.getSheets()[0];
  var range = sheet.getDataRange();
  var values = range.getValues();
  return values;
}

function createObjs(values) {
  var objs = [];
  for(var i = 0; i < values.length; i++) {
    var name = values[i][0];
    var email = values[i][1];
    var obj = {};
    obj['name'] = name;
    obj['email'] = email;
    objs.push(obj);
  }
  return objs;
}



index.html
<!DOCTYPE html>
<html>
<body>
<div id="main_div">
  <input list="list1">
</div>
<script>
/************************************
elem(id)
************************************/
function elem(id) {
  return document.getElementById(id);
}

getData();
/************************************
get_data()
************************************/
function getData() {
  google.script.run
  .withFailureHandler(onFailure)
  .withSuccessHandler(onSuccess)
  .returnData();
}

/************************************
onSuccess(result)
************************************/
function onSuccess(objs) {
  createDatalist(objs);
}

/************************************
onSuccess(result)
************************************/
function createDatalist(objs) {
  alert(JSON.stringify(objs))
  var main = document.getElementById('main_div');
  var datalist = document.createElement('datalist');
  datalist.setAttribute("id", "list1");
  for (var i = 0; i < objs.length; i++) {
    var name = objs[i]['name'];
    var email = objs[i]['email'];
    var option = document.createElement('option');
    option.setAttribute("value", name);
    option.textContent = email;
    datalist.appendChild(option);
  }
  main.appendChild(datalist);
}

/************************************
onFailure(e)
************************************/
function onFailure(e) {
  alert([e.message, e.stack]);
}

</script>
</body>
</html>



Latest post

Extracting data from Google Sheets with regular expressions

Introduction Regular expressions are a powerful tool that can be used to extract data from text.  In Google Sheets, regular expressions ca...