Use 10plate with Google Spreadsheet

With 10plate API, you can connect Google spreadsheet with 10plate templates.

Connect with add-on

With 10plate-sync, you can make spreadsheet sync with 10plate templates.

How to use

1. Open 10plate-sync.
2. Click install button.
3. Accept permission.
   • Add-on requires permission for URL access. This is for accessing to 10plate.io for getting template data only.
4. Open Add-on->10plate sync->Settings
5. Input template URL you want to sync, sheet for initialization, column for showing 10plate.io link.
6. With checking Initialize Header via template scheme, you can insert header row automatically via template's input fields.
7. Save and close settings panel.
8. Input something to spreadsheet, then link will appear. Open link to view template filled with datas in spreadsheet.

Sample:

Source code

main.gs

*execute onEdit on column edited.

/**
 * @OnlyCurrentSpreadsheet
 */

function onInstall(e) {
  onOpen(e);
}

function onOpen(e) {
  SpreadsheetApp.getUi().createAddonMenu().addItem('Settings', 'showSidebar').addToUi();
  init();
}

function onEdit(e){
  var prefs = getPreferences();
  if(e.range && e.range.getSheet().getSheetId() == prefs.sheetId){
    var range = e.range;
    updateButtonAt(e.range.getSheet(),range.getRowIndex());
  }
}

function updateButtonAt(sheet,rowIndex){
  if(rowIndex == 1){
    return;
  }
  var prefs = getPreferences();
  var max = sheet.getMaxColumns();
  var cRange,name,value,targetRange;
  var url = '';
  var params = {};
  var hasParam = false;
  for(var i=1;i<=max;i++){
    cRange = sheet.getRange(rowIndex,i);
    if(cRange.getA1Notation().replace(/[0-9]/g,'') == prefs.buttonColumn){
      targetRange = cRange;
    }else{
      name = sheet.getRange(1, i).getValue();
      value = cRange.getValue();
      if(name && value){
        hasParam = true;
        if(value instanceof Date){
          value = value.toISOString();
        }
        params[name.trim()] = value;
      }
    }
  }
  var url = prefs.templateUrl + '?' + Object.keys(params).reduce(function(a,k){a.push(k+'='+encodeURIComponent(params[k]));return a},[]).join('&');
  if(hasParam){
    targetRange.setFormula('HYPERLINK("'+ url +'","link")');
  }else{
    targetRange.setValue('');
  }
}

function initHeaderViaTemplate(){
  var prefs = getPreferences();
  var url = prefs.templateUrl;
  if(url && url.indexOf('https://10plate.io/t/')===0){
    // get scheme
    var resp = UrlFetchApp.fetch(url+'/meta');
    if(resp.getResponseCode()===200){
      SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(function(sheet){
        if(sheet.getSheetId() == prefs.sheetId){
          sheet.insertRows(1);
          var meta = JSON.parse(resp.getContentText());
          var add = 0;
          var rng;
          for(var i=0,len=meta.schema.length; i<len; i++){
            rng = sheet.getRange(1,i+1+add);
            if(rng.getA1Notation().replace(/[0-9]/g,'') == prefs.buttonColumn){
              add = 1;
              rng = sheet.getRange(1,i+1+add);
            }
            rng.setValue(meta.schema[i].label).setFontWeight('bold');
          }
        }
      });
    }else{
      console.log(resp.getResponseCode());
      var message = prefs.lang == 'ja' ? 'URLが間違っているか、アクセス制限されています' : 'Invalid or Restricted URL';
      var ui = SpreadsheetApp.getUi();
      var response = ui.alert(message);
    }
  }
}

function init(){
  var prefs = getPreferences();
  if(prefs.hasOwnProperty('sheetId')){
    var sheet;
    SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(function(item){
      if(item.getSheetId() == prefs.sheetId){
        sheet = item;
        var maxRow = sheet.getMaxRows();
        for(var i=2;i<=maxRow;i++){
          updateButtonAt(sheet,i);
        }
      }
    });
  }
}                                                      

function showSidebar() {
  var ui = HtmlService.createHtmlOutputFromFile('Settings')
      .setTitle('10plate Settings');
  SpreadsheetApp.getUi().showSidebar(ui);
}


function getPreferences() {
  var documentProperties = PropertiesService.getDocumentProperties();
  var prefs = {
    templateUrl: documentProperties.getProperty('templateUrl'),
    buttonColumn: documentProperties.getProperty('buttonColumn'),
    sheetId: documentProperties.getProperty('sheetId'),
    lang: Session.getActiveUserLocale(),
  };
  return prefs;
}

function setPreferences(templateUrl, buttonColumn,sheetId,initHeader){
  var documentProperties = PropertiesService.getDocumentProperties();
  documentProperties.setProperty('templateUrl', templateUrl);
  documentProperties.setProperty('buttonColumn', buttonColumn);
  documentProperties.setProperty('sheetId', sheetId);
  if(initHeader){
    initHeaderViaTemplate();
  }
  init();
}

function getSheets() {
  return SpreadsheetApp.getActiveSpreadsheet().getSheets().map(function(item){
    return {name:item.getName(), id:item.getSheetId()};
  });
}

Settings.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
    <style>
      body{
        padding:10px;
      }
    </style>
  </head>
  <body>
    <div class="block form-group">
      <label id="xTemplateUrlLabel" for="xTemplateUrl">Template Url</label>
      <input id="xTemplateUrl" />
    </div>
    <div class="block form-group">
      <label id="xTemplateSheetLabel" for="xTemplateSheet">Sheet</label>
      <select id="xSheet"></select>
    </div>
    <div class="block form-group">
      <label id="xTemplateLinkColumnLabel">Show Link in column:</label>
      <input id="xLinkColumn" />
    </div>
    <div class="block form-group" id="xTemplateInitWrap">
      <input type="checkbox" id="xTemplateInitHeader">
      <label id="xTemplateInitHeaderLabel" for="xTemplateInitHeader">Initialize Header via template scheme</label>
    </div>
    <div class="block form-group">
    <button class="action" id="xSubmit" type="submit">save</button>
    </div>
    <p><span class="gray" id="xMessage"></span></p>


    <script src="//ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <script>
      var _prefs;
      $(function(){
        google.script.run
          .withUserObject(this)
          .withSuccessHandler(init)
          .withFailureHandler(err).getPreferences();
      });
      function init(prefs){
        _prefs = prefs;
        var $tmplUrl = $('#xTemplateUrl');
        var $col = $('#xLinkColumn');
        var $sheet = $('#xSheet');
        var $initHeader = $('#xTemplateInitHeader');
        $tmplUrl.val(_prefs.templateUrl);
        $col.val(_prefs.buttonColumn　|| 'A');
        if(_prefs.lang==='ja'){
          $('#xTemplateUrlLabel').text('テンプレートURL');
          $('#xTemplateSheetLabel').text('シート');
          $('#xTemplateLinkColumnLabel').text('リンクを表示する列');
          $('#xTemplateInitHeaderLabel').text('テンプレートを元にヘッダ行を初期化');
        }
        google.script.run
          .withSuccessHandler(initSheets)
          .getSheets();
        $('#xSubmit').on('click',function(){
          google.script.run
            .withSuccessHandler(saved)
            .withFailureHandler(err)
            .setPreferences($tmplUrl.val(),$col.val(),$sheet.val(),$initHeader.prop('checked'));
        });
      }
      function initSheets(sheets){
        var $sheet = $('#xSheet');
        for(var i=0,len=sheets.length;i<len;i++){
          var sheet = sheets[i];
          var name = sheet.name;
          var id = sheet.id;
          var $opt = $('<option>').val(id).text(name).prop('selected',_prefs.sheetId==id);
          $sheet.append($opt);
        }
      }
      function saved(){
        $('#xMessage').text('saved.');
        setTimeout(function(){ $('#xMessage').text('')},5000);
      }
      function err(){
        $('#xMessage').text('error.');
        setTimeout(function(){ $('#xMessage').text('')},5000);
      }
    </script>
  </body>
</html>