google spreadsheet 读取openerp 7.0数据
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [{name: "Insert Selection Field", functionName: "menu_insertSelection"},
{name: "Settings", functionName: "menu_settings"} ];
ss.addMenu("OpenERP", menuEntries);
var a1 = ss.getRange("O60");
if (a1.getFormula().indexOf('oe_settings') > -1 && ScriptProperties.getProperty('url')){
a1.setFormula("");
}
}
function menu_settings() {
var params = [["url", "URL (with http:// or https://)"], ["dbname", "Database Name"], ["username", "Username"], ["password", "Password"]];
for (var i = 0; i < params.length; i++){
var input = Browser.inputBox("Server Settings", params[i][1], Browser.Buttons.OK_CANCEL);
if (input === "cancel"){
break;
}
else{
ScriptProperties.setProperty(params[i][0], input);
}
}
}
function menu_insertSelection() {
var input = Browser.inputBox('Insert Selection', 'Format: model, field, domain', Browser.Buttons.OK_CANCEL);
if (input !== "cancel"){
input = input.replace(/\s+/g, "").split(",");
var model = input[0];
var field = input[1];
var domain = input.slice(2,input.length).join(",");
var range = SpreadsheetApp.getActiveRange();
oe_select(range, model, field, domain);
}
}
function oe_settings(url, dbname, username, password){
if (url)ScriptProperties.setProperty('url', url);
if (dbname)ScriptProperties.setProperty('dbname', dbname);
if (username)ScriptProperties.setProperty('username', username);
if (password)ScriptProperties.setProperty('password', password);
}
function oe_browse(model, fields, domain, sort, limit){
if(typeof model !== "string"){
throw "model arg expecting string";
}
if(typeof fields !== "string"){
throw "fields arg expecting comma separated field names";
}
if (!domain) domain = "[]";
if(typeof domain !== "string"){
throw "domain arg expecting string";
}
if(sort && typeof sort !== "string"){
throw "sort arg expecting string";
}
if(limit && typeof limit !== "number"){
throw "limit arg expecting number";
}
fields = fields.replace(/\s+/g, ",").split(",");
if(domain) {
domain = domain.replace(/\'/g, '"');
}
domain = Utilities.jsonParse(domain);
var records = seach_read(model, fields, domain, sort, limit);
return parse_records_for_ss(records, fields);
}
function oe_read_group(model, fields, groupby, domain, orderby, limit){
if(typeof model !== "string"){
throw "model arg expecting string";
}
if(fields && typeof fields !== "string"){
throw "fields arg expecting comma separated field names";
}
if(groupby && typeof groupby !== "string"){
throw "groupby arg expecting comma separated field names";
}
if (!domain) domain = "[]";
if(typeof domain !== "string"){
throw "domain arg expecting string";
}
if(orderby && typeof orderby !== "string"){
throw "orderby arg expecting string";
}
if(limit && typeof limit !== "number"){
throw "limit arg expecting number";
}
fields = fields ? fields.replace(/\s+/g, ",").split(",") : [];
var fields_tosend = fields.slice();
var count_index = fields_tosend.indexOf("_count");
if (count_index !== -1){
fields_tosend.splice(count_index, 1);
}
groupby = groupby ? groupby.replace(/\s+/g, ",").split(",") : "";
if(domain) {
domain = domain.replace(/\'/g, '"');
}
domain = domain ? Utilities.jsonParse(domain) : [];
var kwargs = {
"context" : {"group_by":groupby},
"domain" : domain,
"fields" : fields_tosend,
"groupby": groupby,
"limit": limit ? limit : 10,
"offset": 0,
"orderby": orderby ? orderby : false,
}
var records = call_kw(model, "read_group", [], {}, 0, kwargs);
if (groupby.length > 0){
for (var i = 0; i < records.length; i++){
if (records[i]["__context"] && records[i]["__context"]["group_by"].length > 0){
kwargs["domain"] = records[i]["__domain"]
kwargs["context"] = records[i]["__context"]
kwargs["groupby"] = records[i]["__context"]["group_by"]
var sub_records = call_kw(model, "read_group", [], {}, 0, kwargs);
sub_records.forEach(function(item){
for(var j = 0; j < this.groupby_fields.length;j++){
item[this.groupby_fields[j]] = records[i][this.groupby_fields[j]]
}
},{
"groupby_fields" : groupby.slice(0,groupby.indexOf(kwargs["groupby"][0]))
});
records.splice.apply(records, [i,1].concat(sub_records));
i--;
}
}
}
var count_index = fields.indexOf('_count');
if (count_index !== -1){
fields[count_index] = groupby instanceof Array && groupby.length > 0 ? groupby[groupby.length-1]+"_count" : groupby+"_count";
}
return parse_records_for_ss(records, fields);
}
function oe_select(range, model, field, domain){
if(typeof model !== "string"){
throw "model arg expecting string";
}
if(typeof field !== "string"){
throw "field arg expecting field name";
}
if (!domain) domain = "[]";
if(typeof domain !== "string"){
throw "domain arg expecting String";
}
var records = oe_read_group(model, field, field, domain);
var dv = range.getDataValidation();
var result = [];
for (var i = 0; i < records.length; i++){
var value = records[i][0];
if (value)result.push(value.replace(",", ""));
}
result = result.slice(0,10);
dv.requireValuesInList(result);
dv.setShowDropDown(true);
range.setDataValidation(dv);
}
function parse_records_for_ss(records, fields){
var result = [];
var types = [];
if (fields.length === 0 && records.length > 0){
fields = Object.keys(records[0]);
result.push(fields);
}
for (var i = 0; i < records.length; i++){
recordArr = [];
for (var j = 0; j < fields.length; j++){
var value = records[i][fields[j]];
if(typeof value === "number")types[fields[j]] = "number";
if (value instanceof Array && value.length === 2 && typeof value[1] === "string")value = value[1];
else if(value instanceof Array) value = value.join(','); //TODO: name_get on ids
else if(typeof value !== "number" && !(value))value = types[fields[j]] && types[fields[j]] === "number" ? 0 : 'Undefined';
recordArr.push(value);
}
result.push(recordArr);
}
return result.length > 0 ? result : 'No Result';
}
function seach_read(model, fields, domain, sort, limit){
if(!(fields instanceof Array)){
throw "fields arg expecting an Array, not "+typeof fields;
}
if (!domain)domain = [];
if(!(domain instanceof Array)){
throw "domain arg expecting an Array, not "+typeof domain;
}
var session_id = getScriptProperty("session_id");
var context = {};
var params = {
"model" : model,
"fields" : fields,
"limit": limit ? limit : 80,
"domain" : domain,
"sort": sort,
"session_id": session_id,
"context": context,
}
var options =
{
"method" : "post",
"contentType" : "application/json",
"payload" : {
"id": 1,
"jsonrpc": "2.0",
"method": "googlescript",
"params" : params,
}
};
var json_result = Utilities.jsonParse(oe_fetch(getScriptProperty('url')+'/web/dataset/search_read', options));
if (!!json_result.error){
throw format_openerp_error(json_result.error);
}
return json_result.result.records;
}
function call_kw(model, method, args, context, debug, kwargs){
if (typeof model !== "string"){
throw "model arg expecting a String, not "+typeof model;
}
if (typeof method !== "string"){
throw "method arg expecting a String, not "+typeof model;
}
if(!(args instanceof Array)){
throw "args arg expecting an Array, not "+typeof args;
}
if(!(context instanceof Object)){
throw "context arg expecting an Object, not "+typeof context;
}
if(typeof debug !== "number"){
throw "debug arg expecting a boolean Number, not "+typeof debug;
}
if(!(kwargs instanceof Object)){
throw "kwargs arg expecting an Object, not "+typeof kwargs;
}
var session_id = getScriptProperty('session_id');
var params = {
"args": args,
"context": context,
"debug" : debug,
"kwargs": kwargs,
"method": method,
"model": model,
"session_id": session_id,
}
var options =
{
"method" : "post",
"contentType" : "application/json",
"payload" : {
"id": 1,
"jsonrpc": "2.0",
"method": "googlescript",
"params" : params,
}
};
var json_result = Utilities.jsonParse(oe_fetch(getScriptProperty('url')+'/web/dataset/call_kw', options));
if (!!json_result.error){
throw format_openerp_error(json_result.error);
}
return json_result.result;
}
function authenticate(){
Logger.log('Authentication requested!');
var url = getScriptProperty("url");
var dbname = getScriptProperty("dbname");
var username = getScriptProperty("username");
var password = getScriptProperty("password");
if (!url || !dbname || !username || !password){
throw "At least one connection detail is not set. You can set them OpenERP > Settings in the menu bar";
}
var params = {
"db": dbname,
"login": username,
"password": password,
}
var options ={
"method" : "post",
"contentType" : "application/json",
"payload" : Utilities.jsonStringify({
"id": 1,
"jsonrpc": "2.0",
"method": "googlescript",
"params" : params,
})
};
var response = UrlFetchApp.fetch(url+'/web/session/authenticate', options);
var json_response = Utilities.jsonParse(response);
if (json_response.result.uid){
var sid = response.getHeaders()["Set-Cookie"].split(" ")[0];
var session_id = json_response.result.session_id;
ScriptProperties.setProperty("sid", sid);
ScriptProperties.setProperty("session_id", session_id)
return {"sid": sid, "session_id": session_id};
}
throw "Authentication Error";
}
function oe_fetch(url, options){
var sid = getScriptProperty("sid");
var session_id = getScriptProperty("session_id");
if (!sid || !session_id){
var authentication = authenticate();
sid = authentication.sid;
session_id = authentication.session_id;
}
if (typeof options.headers === 'undefined')options['headers'] = {'cookie': sid};
else options.headers['cookie'] = sid;
options.payload.params['session_id'] = session_id;
options['payload'] = Utilities.jsonStringify(options.payload);
for (var i = 0; i < 1; i++){
var result = UrlFetchApp.fetch(url, options);
var json_result = Utilities.jsonParse(result);
if (json_result.error && json_result.error.data.type === "client_exception" && json_result.error.data.debug.indexOf("SessionExpiredException") !== -1){
authentication = authenticate();
options['payload'] = Utilities.jsonParse(options.payload);
options.headers['cookie'] = authentication.sid;
options.payload.params['session_id'] = authentication.session_id;
options['payload'] = Utilities.jsonStringify(options.payload);
}
else if(json_result.error){
throw format_openerp_error(json_result.error);
}
else{
return result;
}
}
throw "Unable to fetch data due to session expired exception";
}
function getScriptProperty(key) {
var FailLimit = 100;
var RetryInterval = 50;
var ScriptPropertyValue = "";
var Retries=0;
var randomnumber = 0;
var TryAgain=true;
while (TryAgain)
{
Retries++;
randomnumber=Math.floor(Math.random()*59);
Utilities.sleep(randomnumber*RetryInterval);
Logger.log(randomnumber*RetryInterval);
try
{
TryAgain=false;
ScriptPropertyValue = ScriptProperties.getProperty(key);
}
catch(err)
{
TryAgain = (Retries<FailLimit);
if (!TryAgain){
throw 'Too many attempts to acces script property';
}
continue;
}
return ScriptPropertyValue;
}
}
function format_openerp_error(error){
var error_type = error.data.type;
var trace = "";
if (error_type === "client_exception")trace = error.data.debug;
else if (error_type === "server_exception")trace= error.data.fault_code;
else trace = Utilities.jsonStringify(error.data);
return error.message + ": "+error_type+", "+ trace;
}
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [{name: "Insert Selection Field", functionName: "menu_insertSelection"},
{name: "Settings", functionName: "menu_settings"} ];
ss.addMenu("OpenERP", menuEntries);
var a1 = ss.getRange("O60");
if (a1.getFormula().indexOf('oe_settings') > -1 && ScriptProperties.getProperty('url')){
a1.setFormula("");
}
}
function menu_settings() {
var params = [["url", "URL (with http:// or https://)"], ["dbname", "Database Name"], ["username", "Username"], ["password", "Password"]];
for (var i = 0; i < params.length; i++){
var input = Browser.inputBox("Server Settings", params[i][1], Browser.Buttons.OK_CANCEL);
if (input === "cancel"){
break;
}
else{
ScriptProperties.setProperty(params[i][0], input);
}
}
}
function menu_insertSelection() {
var input = Browser.inputBox('Insert Selection', 'Format: model, field, domain', Browser.Buttons.OK_CANCEL);
if (input !== "cancel"){
input = input.replace(/\s+/g, "").split(",");
var model = input[0];
var field = input[1];
var domain = input.slice(2,input.length).join(",");
var range = SpreadsheetApp.getActiveRange();
oe_select(range, model, field, domain);
}
}
function oe_settings(url, dbname, username, password){
if (url)ScriptProperties.setProperty('url', url);
if (dbname)ScriptProperties.setProperty('dbname', dbname);
if (username)ScriptProperties.setProperty('username', username);
if (password)ScriptProperties.setProperty('password', password);
}
function oe_browse(model, fields, domain, sort, limit){
if(typeof model !== "string"){
throw "model arg expecting string";
}
if(typeof fields !== "string"){
throw "fields arg expecting comma separated field names";
}
if (!domain) domain = "[]";
if(typeof domain !== "string"){
throw "domain arg expecting string";
}
if(sort && typeof sort !== "string"){
throw "sort arg expecting string";
}
if(limit && typeof limit !== "number"){
throw "limit arg expecting number";
}
fields = fields.replace(/\s+/g, ",").split(",");
if(domain) {
domain = domain.replace(/\'/g, '"');
}
domain = Utilities.jsonParse(domain);
var records = seach_read(model, fields, domain, sort, limit);
return parse_records_for_ss(records, fields);
}
function oe_read_group(model, fields, groupby, domain, orderby, limit){
if(typeof model !== "string"){
throw "model arg expecting string";
}
if(fields && typeof fields !== "string"){
throw "fields arg expecting comma separated field names";
}
if(groupby && typeof groupby !== "string"){
throw "groupby arg expecting comma separated field names";
}
if (!domain) domain = "[]";
if(typeof domain !== "string"){
throw "domain arg expecting string";
}
if(orderby && typeof orderby !== "string"){
throw "orderby arg expecting string";
}
if(limit && typeof limit !== "number"){
throw "limit arg expecting number";
}
fields = fields ? fields.replace(/\s+/g, ",").split(",") : [];
var fields_tosend = fields.slice();
var count_index = fields_tosend.indexOf("_count");
if (count_index !== -1){
fields_tosend.splice(count_index, 1);
}
groupby = groupby ? groupby.replace(/\s+/g, ",").split(",") : "";
if(domain) {
domain = domain.replace(/\'/g, '"');
}
domain = domain ? Utilities.jsonParse(domain) : [];
var kwargs = {
"context" : {"group_by":groupby},
"domain" : domain,
"fields" : fields_tosend,
"groupby": groupby,
"limit": limit ? limit : 10,
"offset": 0,
"orderby": orderby ? orderby : false,
}
var records = call_kw(model, "read_group", [], {}, 0, kwargs);
if (groupby.length > 0){
for (var i = 0; i < records.length; i++){
if (records[i]["__context"] && records[i]["__context"]["group_by"].length > 0){
kwargs["domain"] = records[i]["__domain"]
kwargs["context"] = records[i]["__context"]
kwargs["groupby"] = records[i]["__context"]["group_by"]
var sub_records = call_kw(model, "read_group", [], {}, 0, kwargs);
sub_records.forEach(function(item){
for(var j = 0; j < this.groupby_fields.length;j++){
item[this.groupby_fields[j]] = records[i][this.groupby_fields[j]]
}
},{
"groupby_fields" : groupby.slice(0,groupby.indexOf(kwargs["groupby"][0]))
});
records.splice.apply(records, [i,1].concat(sub_records));
i--;
}
}
}
var count_index = fields.indexOf('_count');
if (count_index !== -1){
fields[count_index] = groupby instanceof Array && groupby.length > 0 ? groupby[groupby.length-1]+"_count" : groupby+"_count";
}
return parse_records_for_ss(records, fields);
}
function oe_select(range, model, field, domain){
if(typeof model !== "string"){
throw "model arg expecting string";
}
if(typeof field !== "string"){
throw "field arg expecting field name";
}
if (!domain) domain = "[]";
if(typeof domain !== "string"){
throw "domain arg expecting String";
}
var records = oe_read_group(model, field, field, domain);
var dv = range.getDataValidation();
var result = [];
for (var i = 0; i < records.length; i++){
var value = records[i][0];
if (value)result.push(value.replace(",", ""));
}
result = result.slice(0,10);
dv.requireValuesInList(result);
dv.setShowDropDown(true);
range.setDataValidation(dv);
}
function parse_records_for_ss(records, fields){
var result = [];
var types = [];
if (fields.length === 0 && records.length > 0){
fields = Object.keys(records[0]);
result.push(fields);
}
for (var i = 0; i < records.length; i++){
recordArr = [];
for (var j = 0; j < fields.length; j++){
var value = records[i][fields[j]];
if(typeof value === "number")types[fields[j]] = "number";
if (value instanceof Array && value.length === 2 && typeof value[1] === "string")value = value[1];
else if(value instanceof Array) value = value.join(','); //TODO: name_get on ids
else if(typeof value !== "number" && !(value))value = types[fields[j]] && types[fields[j]] === "number" ? 0 : 'Undefined';
recordArr.push(value);
}
result.push(recordArr);
}
return result.length > 0 ? result : 'No Result';
}
function seach_read(model, fields, domain, sort, limit){
if(!(fields instanceof Array)){
throw "fields arg expecting an Array, not "+typeof fields;
}
if (!domain)domain = [];
if(!(domain instanceof Array)){
throw "domain arg expecting an Array, not "+typeof domain;
}
var session_id = getScriptProperty("session_id");
var context = {};
var params = {
"model" : model,
"fields" : fields,
"limit": limit ? limit : 80,
"domain" : domain,
"sort": sort,
"session_id": session_id,
"context": context,
}
var options =
{
"method" : "post",
"contentType" : "application/json",
"payload" : {
"id": 1,
"jsonrpc": "2.0",
"method": "googlescript",
"params" : params,
}
};
var json_result = Utilities.jsonParse(oe_fetch(getScriptProperty('url')+'/web/dataset/search_read', options));
if (!!json_result.error){
throw format_openerp_error(json_result.error);
}
return json_result.result.records;
}
function call_kw(model, method, args, context, debug, kwargs){
if (typeof model !== "string"){
throw "model arg expecting a String, not "+typeof model;
}
if (typeof method !== "string"){
throw "method arg expecting a String, not "+typeof model;
}
if(!(args instanceof Array)){
throw "args arg expecting an Array, not "+typeof args;
}
if(!(context instanceof Object)){
throw "context arg expecting an Object, not "+typeof context;
}
if(typeof debug !== "number"){
throw "debug arg expecting a boolean Number, not "+typeof debug;
}
if(!(kwargs instanceof Object)){
throw "kwargs arg expecting an Object, not "+typeof kwargs;
}
var session_id = getScriptProperty('session_id');
var params = {
"args": args,
"context": context,
"debug" : debug,
"kwargs": kwargs,
"method": method,
"model": model,
"session_id": session_id,
}
var options =
{
"method" : "post",
"contentType" : "application/json",
"payload" : {
"id": 1,
"jsonrpc": "2.0",
"method": "googlescript",
"params" : params,
}
};
var json_result = Utilities.jsonParse(oe_fetch(getScriptProperty('url')+'/web/dataset/call_kw', options));
if (!!json_result.error){
throw format_openerp_error(json_result.error);
}
return json_result.result;
}
function authenticate(){
Logger.log('Authentication requested!');
var url = getScriptProperty("url");
var dbname = getScriptProperty("dbname");
var username = getScriptProperty("username");
var password = getScriptProperty("password");
if (!url || !dbname || !username || !password){
throw "At least one connection detail is not set. You can set them OpenERP > Settings in the menu bar";
}
var params = {
"db": dbname,
"login": username,
"password": password,
}
var options ={
"method" : "post",
"contentType" : "application/json",
"payload" : Utilities.jsonStringify({
"id": 1,
"jsonrpc": "2.0",
"method": "googlescript",
"params" : params,
})
};
var response = UrlFetchApp.fetch(url+'/web/session/authenticate', options);
var json_response = Utilities.jsonParse(response);
if (json_response.result.uid){
var sid = response.getHeaders()["Set-Cookie"].split(" ")[0];
var session_id = json_response.result.session_id;
ScriptProperties.setProperty("sid", sid);
ScriptProperties.setProperty("session_id", session_id)
return {"sid": sid, "session_id": session_id};
}
throw "Authentication Error";
}
function oe_fetch(url, options){
var sid = getScriptProperty("sid");
var session_id = getScriptProperty("session_id");
if (!sid || !session_id){
var authentication = authenticate();
sid = authentication.sid;
session_id = authentication.session_id;
}
if (typeof options.headers === 'undefined')options['headers'] = {'cookie': sid};
else options.headers['cookie'] = sid;
options.payload.params['session_id'] = session_id;
options['payload'] = Utilities.jsonStringify(options.payload);
for (var i = 0; i < 1; i++){
var result = UrlFetchApp.fetch(url, options);
var json_result = Utilities.jsonParse(result);
if (json_result.error && json_result.error.data.type === "client_exception" && json_result.error.data.debug.indexOf("SessionExpiredException") !== -1){
authentication = authenticate();
options['payload'] = Utilities.jsonParse(options.payload);
options.headers['cookie'] = authentication.sid;
options.payload.params['session_id'] = authentication.session_id;
options['payload'] = Utilities.jsonStringify(options.payload);
}
else if(json_result.error){
throw format_openerp_error(json_result.error);
}
else{
return result;
}
}
throw "Unable to fetch data due to session expired exception";
}
function getScriptProperty(key) {
var FailLimit = 100;
var RetryInterval = 50;
var ScriptPropertyValue = "";
var Retries=0;
var randomnumber = 0;
var TryAgain=true;
while (TryAgain)
{
Retries++;
randomnumber=Math.floor(Math.random()*59);
Utilities.sleep(randomnumber*RetryInterval);
Logger.log(randomnumber*RetryInterval);
try
{
TryAgain=false;
ScriptPropertyValue = ScriptProperties.getProperty(key);
}
catch(err)
{
TryAgain = (Retries<FailLimit);
if (!TryAgain){
throw 'Too many attempts to acces script property';
}
continue;
}
return ScriptPropertyValue;
}
}
function format_openerp_error(error){
var error_type = error.data.type;
var trace = "";
if (error_type === "client_exception")trace = error.data.debug;
else if (error_type === "server_exception")trace= error.data.fault_code;
else trace = Utilities.jsonStringify(error.data);
return error.message + ": "+error_type+", "+ trace;
}
=oe_browse("res.partner";"name city street country_id";"[['supplier','=',true]]";"city desc, name desc";10)
=oe_browse("res.partner";"name city street country_id";"[['supplier','=',true]]";"city asc, name desc";)
=oe_browse("res.partner";"name city street country_id")
