Google Spreadsheetとの連携
APIを活用すると、10plate上のテンプレートと、Google Spreadsheetを連携させることができます。
アドオンを使って連携させる
10plate-syncというアドオンを使うと、テンプレートと連携するSpreadsheetを簡単に作ることができます。
使い方
- 10plate-syncのページを開きます
- 「+ 無料」と書かれたボタンをクリックします
- スプレッドシートが開きます。
- 承認を求める画面が出ますので承認してください
- URLアクセスの権限を求めますが、これは10plate.ioへのアクセスが必要なためです。他のところにデータを送るような事はありません。
- アドオン>10plate sync>Settingsを開いてください
- 連携させたいテンプレートのURLと、連携させるシート、入力した時に10plateへのリンクを表示させる列を指定してください
- 「テンプレートを元に初期化」にチェックを入れる事で、テンプレート側の必要な入力項目が、自動で1行目に挿入されます
- 保存して、Settingsパネルを閉じてください。
- スプレッドシートにデータを書き込むと、リンクが表示されます。このリンクを開くと、テンプレートが入力した内容で埋まった状態で表示されます。
サンプル:
ソースコード(最新のものとは異なる場合があります)
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>