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

Popular posts from this blog

ubuntu - PHP script to find files of certain extensions in a directory, returns populated array when run in browser, but empty array when run from terminal -

php - How can i create a user dashboard -

javascript - How to detect toggling of the fullscreen-toolbar in jQuery Mobile? -