/* 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 = "
" + "" + ""; 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 = "" + getLabel(2) + "
"; 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 = "" + "" + "<< BACK
" + dirs.next().getName() + "
"; } if(items == getLabel(4) + "@"){ items = "Empty:)....
"; } 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 += "" + "" + dir + "/" + file.getName() + "
"; } if(csvhtm == getLabel(5) + "@"){ csvhtm += "Empty:)....
"; } 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; }