php - connect to google sheet and MySQL database -
when add new row or edit row in sheet, auto-update in mysql database table. after 1 hour, automatic update google sheet mysql database table.
how can connect google sheet , mysql?
first, tried code:
var format_oneline = 'one-line'; var format_multiline = 'multi-line'; var format_pretty = 'pretty'; var language_js = 'javascript'; var language_python = 'python'; var structure_list = 'list'; var structure_hash = 'hash (keyed "id" column)'; /* defaults particular spreadsheet, change desired */ var default_format = format_pretty; var default_language = language_js; var default_structure = structure_list; function onopen() { var ss = spreadsheetapp.getactivespreadsheet(); var menuentries = [ {name: "export json sheet", functionname: "exportsheet"}, {name: "export json sheets", functionname: "exportallsheets"}, {name: "configure export", functionname: "exportoptions"}, ]; ss.addmenu("export json", menuentries); } function exportoptions() { var doc = spreadsheetapp.getactivespreadsheet(); var app = uiapp.createapplication().settitle('export json'); var grid = app.creategrid(4, 2); grid.setwidget(0, 0, makelabel(app, 'language:')); grid.setwidget(0, 1, makelistbox(app, 'language', [language_js, language_python])); grid.setwidget(1, 0, makelabel(app, 'format:')); grid.setwidget(1, 1, makelistbox(app, 'format', [format_pretty, format_multiline, format_oneline])); grid.setwidget(2, 0, makelabel(app, 'structure:')); grid.setwidget(2, 1, makelistbox(app, 'structure', [structure_list, structure_hash])); grid.setwidget(3, 0, makebutton(app, grid, 'export active sheet', 'exportsheet')); grid.setwidget(3, 1, makebutton(app, grid, 'export sheets', 'exportallsheets')); app.add(grid); doc.show(app); } function makelabel(app, text, id) { var lb = app.createlabel(text); if (id) { lb.setid(id); return lb; } function makelistbox(app, name, items) { var listbox = app.createlistbox().setid(name).setname(name); listbox.setvisibleitemcount(1); var cache = cacheservice.getpubliccache(); var selectedvalue = cache.get(name); logger.log(selectedvalue); (var = 0; <items.length; i++) { listbox.additem(items[i]); if (items[1] == selectedvalue) { listbox.setselectedindex(i); } } return listbox; } function makebutton(app, parent, name, callback) { var button = app.createbutton(name); app.add(button); var handler = app.createserverclickhandler(callback).addcallbackelement(parent);; button.addclickhandler(handler); return button; } function maketextbox(app, name) { var textarea = app.createtextarea().setwidth('100%').setheight('200px').setid(name).setname(name); return textarea; } function exportallsheets(e) { var ss = spreadsheetapp.getactivespreadsheet(); var sheets = ss.getsheets(); var sheetsdata = {}; (var = 0; < sheets.length; i++) { var sheet = sheets[i]; var rowsdata = getrowsdata_(sheet, getexportoptions(e)); var sheetname = sheet.getname(); sheetsdata[sheetname] = rowsdata; } var json = makejson_(sheetsdata, getexportoptions(e)); return displaytext_(json); } function exportsheet(e) { var ss = spreadsheetapp.getactivespreadsheet(); var sheet = ss.getactivesheet(); var rowsdata = getrowsdata_(sheet, getexportoptions(e)); var json = makejson_(rowsdata, getexportoptions(e)); return displaytext_(json); } function getexportoptions(e) { var options = {}; options.language = e && e.parameter.language || default_language; options.format = e && e.parameter.format || default_format; options.structure = e && e.parameter.structure || default_structure; var cache = cacheservice.getpubliccache(); cache.put('language', options.language); cache.put('format', options.format); cache.put('structure', options.structure); logger.log(options); return options; } function makejson_(object, options) { if (options.format == format_pretty) { var jsonstring = json.stringify(object, null, 4); } else if (options.format == format_multiline) { var jsonstring = utilities.jsonstringify(object); jsonstring = jsonstring.replace(/},/gi, '},\n'); jsonstring = prettyjson.replace(/":\[{"/gi, '":\n[{"'); jsonstring = prettyjson.replace(/}\],/gi, '}],\n'); } else { var jsonstring = utilities.jsonstringify(object); } if (options.language == language_python) { // add unicode markers jsonstring = jsonstring.replace(/"([a-za-z]*)":\s+"/gi, '"$1": u"'); } return jsonstring; } function displaytext_(text) { var app = uiapp.createapplication().settitle('exported json'); app.add(maketextbox(app, 'json')); app.getelementbyid('json').settext(text); var ss = spreadsheetapp.getactivespreadsheet(); ss.show(app); return app; } // getrowsdata iterates row row in input range , returns array of objects. // each object contains data given row, indexed normalized column name. // arguments: // - sheet: sheet object contains data processed // - range: exact range of cells data stored // -> columnheadersrowindex: specifies row number column names stored. // argument optional , defaults row above range; // returns array of objects. function getrowsdata_(sheet, options) { var headersrange = sheet.getrange(1, 1, sheet.getfrozenrows(), sheet.getmaxcolumns()); var headers = headersrange.getvalues()[0]; var datarange = sheet.getrange(sheet.getfrozenrows()+1, 1, sheet.getmaxrows(), sheet.getmaxcolumns()); var objects = getobjects_(datarange.getvalues(), normalizeheaders_(headers)); if (options.structure == structure_hash) { var objectsbyid = {}; objects.foreach(function(object) { objectsbyid[object.id] = object; }); return objectsbyid; } else { return objects; } } //getcolumnsdata iterates column column in input range , returns array of objects. // each object contains data given column, indexed normalized row name. // arguments: //- sheet: sheet object contains data processed //- range: exact range of cells data stored //- rowheaderscolumnindex: specifies column number row names stored. //this argument optional , defaults column left of range; // returns array of objects. function getcolumnsdata_(sheet, range, rowheaderscolumnindex) { rowheaderscolumnindex = rowheaderscolumnindex || range.getcolumnindex() - 1; var headerstmp = sheet.getrange(range.getrow(), rowheaderscolumnindex, range.getnumrows(), 1).getvalues(); var headers = normalizeheaders_(arraytranspose_(headerstmp)[0]); return getobjects(arraytranspose_(range.getvalues()), headers); } // every row of data in data, generates object contains data. names of // object fields defined in keys. // arguments: // - data: javascript 2d array // - keys: array of strings define property names objects create function getobjects_(data, keys) { var objects = []; (var = 0; <data.length; ++i) { var object = {}; var hasdata = false; (var j = 0; j < data[i].length; ++j) { var celldata = data[i][j]; if (iscellempty_(celldata)) { continue; } object[keys[j]] = celldata; hasdata = true; } if (hasdata) { objects.push(object); } } return objects; } // returns array of normalized strings. // arguments: // - headers: array of strings normalize function normalizeheaders_(headers) { var keys = []; (var = 0; <headers.length; ++i) { var key = normalizeheader_(headers[i]); if (key.length > 0) { keys.push(key); } } return keys; } // normalizes string, removing alphanumeric characters , using mixed case // separate words. output start lower case letter. // function designed produce javascript object property names. // arguments: // - header: string> normalize // examples: // "first name" -> "firstname" // "market cap (millions) -> "marketcapmillions // "1 number @ beginning ignored" -> "numberatthebeginningisignored" function normalizeheader_(header) { var key = ""; var uppercase = false; (var = 0; < header.length; ++i) { var letter = header[i]; if (letter == " " && key.length > 0) { uppercase = true; continue; } if (!isalnum_(letter)) { continue; } if (key.length == 0 && isdigit_(letter)) { continue; // first character must letter } if (uppercase) { uppercase = false; key += letter.touppercase(); } else { key += letter.tolowercase(); } } return key; } // returns true if cell celldata read empty. //arguments: // - celldata: string function iscellempty_(celldata) { return typeof(celldata) == "string" && celldata == ""; } // returns true if character char alphabetical, false otherwise. function isalnum_(char) { return char >= 'a' && char <= 'z' || char >= 'a' && char <= 'z' || isdigit_(char); } //returns true if character char digit, false otherwise. function isdigit_(char) { return char >= '0' && char <= '9'; } // given javascript 2d array, function returns transposed table. // arguments: // - data: javascript 2d array // returns javascript 2d array // example: arraytranspose([[1,2,3],[4,5,6]]) returns [[1,4],[2,5],[3,6]]. function arraytranspose_(data) { if (data.length == 0 || data[0].length == 0) { return null; } var ret = []; (var = 0; < data[0].length; ++i) { ret.push([]); } (var = 0; < data.length; ++i) { (var j = 0; j < data[i].length; ++j) { ret[j][i] = data[i][j]; } } return ret; }
copied code in tool->script-editor doesn't work, shows authentication problem in google sheet.
Comments
Post a Comment