このブログは広告及びアフェリエイトプログラムを使用しています。

プライバシーポリシーと免責事項

スプレッドシート版Formecollector2について1

December 9, 2020
google spreadsheet データ入力 Forme Pro Forme go
執筆:EugeneAmnis

スプレッドシート版Formecollector2について1

FormeCollector2のGoogleスプレッドシート版であるFormeCollector2DEMOを公開しました。名前の通り、コードのデモを主な目的にしたものですが、手順を踏む事で使用が可能になります。今回はコード版の開発意図と使用方法を説明していきます。

開発意図

FormeシリーズはオフラインにもITの恩恵をのスタイルで開発を続けてきました。Formeのインストールされている携帯端末とパーソナルコンピュータがあれば情報収集が紙よりも簡単に早くできるようになる。これはオフラインのペーパーレス化を推進するものです。その為、オフラインでも使用する事が可能なMicrosoft ExcelやLibreOffice Calcを重視してFormeCollector2の開発を行なっていました。

しかし、今回のコロナ禍で現実世界ではオンラインとオフラインが密接に絡まり合っていることを痛感させられました。情報収集にFAXやExcel、紙を使い、オンラインのシステムに入力するなど、ペーパーレス化やオンラインのラストワンマイルを見せつけらたとき、今まで及び腰だったオンラインシステムであるGoogleスプレッドシート上でのFormeCollector2の開発を着手しました。

全てオフラインで完結するのではなく、オンラインのラストワンマイルを保管する道具としてFormeを使ってもらうにはどうすればよいかの私なりのひとつの答えです。

シートの開示ではなくアドインとして公開する事がユーザーに取ってはアクセスしやすいとは思いますが、すでにAndroid App、Excel、Calc等の開発と保全で余裕がない状態な為にGoogleスプレッドシートのアドインの継続的な開発と保全が約束できないのでコードとシートの開示という措置を取りました。

運用方法

FormeCollector2の基本機能のみを実装しています。一部挙動が違うところがあります。

CSVデータを追加では他のFormeCollector2と違い、フォルダ内からデータを移動することはありません。またフォルダはルート内にある必要があります。

コード版の準備

使用するためには、ご自身のGoogleドライブ上で新しくGoogleスプレッドシートを作成 > スクリプトエディタを起動 > 以下のコードを貼り付ける > マクロを実行し権限を与える必要があります。

尚、コードはjavascriptの形式でこちらからダウンロードできます。

/* 
    Created on : 2020/12/08
    Author     : eugenes works(https://eugenesworks.com)
    Home page  : https://the-forme.net
    Version    :1.0
    MIT LICENSE
 */

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var addmenu = ui.createMenu("Forme2");
  addmenu.addItem(getLabel(0), "createQR");
  addmenu.addItem(getLabel(1),"addCsvMain");
  addmenu.addSeparator();
  addmenu.addItem("ABOUT", "info");
  addmenu.addToUi();
}

function createQR(){
  var ranges = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveRange();
  var vals = ranges.getValues();
  var qrval = "";
  
  var cmax = 0,rownos = [1,2],name = vals[0][0];
  
  if(name.indexOf("#") == 0){
    qrval = getFormeFormat(vals);
  }else{
    qrval = getTabFormat(vals);
  }
  
  qrval = unescape(encodeURIComponent(qrval));
  
  var qrcode = "<!DOCTYPE html><html><head><style>body{text-align:center;margin:0 auto;}</style></head><body><div id='qr'></div>" + 
    "<script src='https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js'></script>" + 
      "<script src='https://cdnjs.cloudflare.com/ajax/libs/jquery.qrcode/1.0/jquery.qrcode.min.js'></script><script>" + 
        "var qrv = '" + qrval + "';jQuery('#qr').qrcode({width:250,height:250,text:qrv,});</script></body></html>";
  var qr = HtmlService.createHtmlOutput(qrcode);
  SpreadsheetApp.getUi().showModalDialog(qr, getLabel(0));
}

function addCsvMain(){
  var sas = SpreadsheetApp.getActiveSpreadsheet();
  var apath = "",　csvs = [];
  for(var i = 0;i < sas.getSheets().length;i++){
    apath = sas.getSheets()[i].getRange("A1").getValue();
    if(apath.indexOf("FormePath:") == 0){
      csvs.push(i);
    }
  }
  if(csvs.length == 0){
    createCsvSheet();
  }else if(csvs.length == 1){
    addCsvRecords(sas.getSheets()[csvs[0]]);
  }else{
    Browser.msgBox(getLabel(7));
  }
}

function info(){
  var htmcode = "<!DOCTYPE html><html><head><style>h1{color:#16ac00;}a{color:#16ac00;}</style></head><body><h1>FormeCollector2DEMO" + 
    "<small><br> is MIT License.</small></h1><p>" + 
    getLabel(2) + "</p><p><a href='" + getLabel(3) +"'>HOMEPAGE</a></p></body></html>";
  var htm = HtmlService.createHtmlOutput(htmcode);
  SpreadsheetApp.getUi().showModalDialog(htm, "ABOUT");
}

function getFormeFormat(vals){
  var qrval = "";
  var cmax = 0,rownos = [1,2],cols,colsval = "";
  var name = vals[0][0],code = "",questions = "",answers = "";
  
  name = name.slice(1, name.length) + "]";
  
  questions = vals[rownos[0]].join("[");
  questions += "]";
  
  code = vals[rownos[1]].join("");
  code += "]";
  
  vals.shift();
  vals.shift();
  vals.shift();
  
  cmax = vals[0].length;
  for(var c = 0;c < cmax;c++){
    cols = vals.map(function(value,index) { return value[c]; });
    colsval = cols.join("");
    if(colsval.length > 0){
      answers += cols.join(";");
    }else{
      answers += "[";
    }
  }
   
  qrval = name + code + questions + answers + " ";  
  return qrval;
}

function getTabFormat(vals){
  var qrval = "",r = 0,rmax = vals.length;
  
  for(r;r < rmax;r++){
    qrval += vals[r].join("\t");
    qrval += "\\n";
  }
    
  return qrval;
}

function createCsvSheet(){
  var item = getDirs();
  var items = item.split("@");
  
  var addcode = "<!DOCTYPE html><html><head>" + 
    "<style>.head{position:fixed;top:0;left:0;z-index:10;background-color:white;width:100%;}h3{color:#16ac00;}" + 
      "#item-ctr{padding-top:50px;padding-bottom:50px;}p{color:black;}p:hover{background-color:silver;color:#16ac00;}" + 
        "p:active{background-color:#16ac00;color:white;}small{color:silver;font-size:small;}" + 
        "#cancel{position:fixed;bottom:0;left:0;z-index:10;background-color:white;text-align:left;display:none;}" + 
          "#cancel p{color:blue;}#cancel p:active{background-color:blue;color:white;}</style></head>" + 
            "<body><div class='head'><h3 id='lbl'>" + getLabel(4) + "</h3></div><div id='item-ctr'>" + items[1] + "</div>" + 
              "<div id='cancel'><p onclick='dirback();'>&lt;&lt; BACK</p></div>" + 
        "<script>var lbl = document.getElementById('lbl');var ctr = document.getElementById('item-ctr');var ccl = document.getElementById('cancel');" + 
          "function selectCsv(v){google.script.run.withSuccessHandler(" + 
            "function(ele){var els = ele.split('@');lbl.innerText = els[0];ctr.innerHTML = els[1];ccl.style.display = 'block';})" + 
              ".withFailureHandler(function(){google.script.host.close();}).getCsvs(v);}" + 
            "function dirback(){google.script.run.withSuccessHandler(" + 
              "function(ele){var els = ele.split('@');lbl.innerText = els[0];ctr.innerHTML = els[1];ccl.style.display = 'none';})" + 
                ".withFailureHandler(function(){google.script.host.close();}).getDirs();}" +
                  "function setCsv(v){ctr.innerHTML = '<p>Loading...</p>';ccl.style.display = 'none';" + 
              "google.script.run.withSuccessHandler(function(){google.script.host.close();})" + 
              ".withFailureHandler(function(){google.script.host.close();}).setFormePath(v);}" + 
        "</script></body></html>";
  var addhtm = HtmlService.createHtmlOutput(addcode);
  SpreadsheetApp.getUi().showModalDialog(addhtm, getLabel(1));
}

function getDirs(){
  var root = DriveApp.getRootFolder();
  var dirs = root.getFolders();
  var items = getLabel(4) + "@";
  
  while(dirs.hasNext()){
    items += "<p onclick='selectCsv(this.innerText);'>" +  dirs.next().getName() + "</p>";
  }
  
  if(items == getLabel(4) + "@"){
    items = "<p style='text-align:center;margin:0 auto;color:red;'>Empty:)....</p>";
  }
  
  return items;
}

function getCsvs(dir){
  var csvhtm = getLabel(5) + "@";
  var dirpath = DriveApp.getFoldersByName(dir);
  var files = dirpath.next().getFilesByType("text/csv");
  var file = "";
  
  while(files.hasNext()){
    file = files.next();
    csvhtm += "<p onclick='setCsv(this.innerText);'>" + "<small>" + dir + "/</small>" + file.getName() + "</p>";
  }
  
  if(csvhtm == getLabel(5) + "@"){
    csvhtm += "<p style='text-align:center;margin:0 auto;color:red;'>Empty:)....</p>";
  }
  
  return csvhtm;
}

function setFormePath(v){
  if(v.indexOf("_") == -1){
    Browser.msgBox(getLabel(6));
  }else{
    var keyword = v.split("_")[0];
    var sht = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
    sht.activate();
    sht.getRange("A1").setValue("FormePath:" + keyword);
    addCsvRecords(sht);
  }
}

function addCsvRecords(sht){
  var current = new Date(),diff;
  
  sht.activate();
  
  var lastrow = sht.getLastRow();
  var afterfiles = [],headv;
  
  var path = sht.getRange("A1").getValue();
  path = path.replace("FormePath:","");
  var paths = path.split("/");
  
  if(paths[0].length > 0 && paths[1].length > 0){
    var dirpath = DriveApp.getFoldersByName(paths[0]);
    
    headv = sht.getRange(1,2,1,sht.getLastColumn() - 1).getValues().join();
    
    if(lastrow > 0){
      afterfiles = sht.getRange(2,1,lastrow).getValues();
    }
    
    afterfiles = afterfiles.map(function(value,index) { return value[0];});
    afterfiles = afterfiles.filter(function(x, i, self) {return self.indexOf(x) === i;});
    
    var files = dirpath.next().getFilesByType("text/csv");
    var file = "",data,csv,csvval = "";
            
    while(files.hasNext()){
      file = files.next();
      if(afterfiles.indexOf(file.getName()) == -1){
        if(file.getName().indexOf(paths[1]) >= 0){
          try{
            data = file.getBlob().getDataAsString();
            csv = Utilities.parseCsv(data);
            
            for (var i = 0; i < csv.length - 1; i++){
              if(headv != csv[i].join()){
                csv[i].unshift(file.getName());
                sht.appendRow(csv[i]);
              }
            } 
          }catch(e){
    
          }
        }
      }
      diff = parseInt((new Date().getTime() - current.getTime()) / (1000 * 60));
      if(diff > 3){break;}
    }
  }else{
    SpreadsheetApp.getActive().deleteActiveSheet();
    Browser.msgBox("INDEX ERROR");
  }
}

function getLabel(sw){
 var lang = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetLocale();
 var mess = "";
  if(lang == "ja_JP"){
    switch(sw){
      case 0:
        mess = "QRコードを作成";
        break;
      case 1:
        mess = "CSVデータを追加";
        break;
      case 2:
        mess = "このスプレッドシートは AndroidApp である Forme との連携のデモとして作成されました。";
        break;
      case 3:
        mess = "https://the-forme.net";
        break;
      case 4:
        mess = "参照フォルダを選択してください。";
        break;
      case 5:
        mess = "追加するCSVデータを選んでください。";
        break;
      case 6:
        mess = "FormeCollector用のCSVデータではありません。";
        break;
      case 7:
        mess = "A1セルに'FormePath:xxx'が入力されたシートが複数あります。このバージョンでは対応しておりません。Excel または LibreOffice バージョンをご利用してください。";
        break;
    }
  }else{
    switch(sw){
      case 0:
        mess = "Create a QR code";
        break;
      case 1:
        mess = "Add CSV data";
        break;
      case 2:
        mess = "This spreadsheet was created as a demo of working with Forme, an AndroidApp.";
        break;
      case 3:
        mess = "https://www.the-forme.net/about_en.html";
        break;
      case 4:
        mess = "Please select a reference folder.";
        break;
      case 5:
        mess = "Select the CSV data to add.";
        break;
      case 6:
        mess = "It is not CSV data for Forme Collector.";
        break;
      case 7:
        mess = "There are multiple sheets with'FormePath: xxx'in cell A1. Not supported in this version. Please use Excel or LibreOffice version.";
        break;
    }
  }
  return mess;
}

次回は公開されたシートを使用する為の準備について説明します。

Next Previous

Contact and Tags

MailForm

google spreadsheet データ入力 Forme Pro Forme go

Related and Home

Home

カミの扱い

November 7, 2024
データ入力 操作 ForMe FormeStudio QC KAIZEN

Formeシステムのハードウエア要件

November 7, 2024
excel Excel for Mac Mac データ入力 ForMe FormeStudio QC KAIZEN

写真つきフォーム機能

May 14, 2024
データ入力 操作 ForMe QC KAIZEN