时间戳

首页 > 解决方案 > 用于在 Google Workspace 中创建数据库的应用脚本代码

mysql - 用于在 Google Workspace 中创建数据库的应用脚本代码

问题描述

我有应用程序脚本代码来创建当前在 Google 表格中使用的数据库。我似乎无法在互联网上找到任何关于如何编辑代码以便能够在 Google Workspace 上运行的帮助。我添加了“Area120Tables”和“Sheets”服务。我将包括适用于我的工作表数据库的当前应用程序脚本代码。

var server = web.server;
var dbName = web.dbName;
var username = web.username;
var password = web.password;
var port = web.port;
var url = web.url;


    
function createConnction() {
  var conn = Jdbc.getConnection(url, username, password);
  Logger.log(conn)
  conn.close();
}

function create_Db() {
  var conn = Jdbc.getConnection(url, username, password);
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName("DataBase");
  //sheet.getRange('A5:X400').clearContent();
  var stmt = conn.createStatement();
  //remove the set filter if one exist
  //  removeFilter();

  stmt.setMaxRows(500);
  
  var results = stmt.executeQuery('(SELECT  housemaster.address1 as Address,date_format(housemaster.ratified_date, "%m/%d/%Y") as Ratified_Date, ' 
  + ' CONCAT(LEFT(housemaster.homephone,3),"-", MID(housemaster.homephone,4,3),"-", RIGHT(housemaster.homephone,4)) as Phone, '
      + ' (CASE WHEN housemaster.conststart_date is Null OR (DateDiff(housemaster.conststart_date,housemaster.contract_date)) >= 0 Then "NO" ELSE "YES" END) AS Spec, '
      + ' CONCAT(prospectmaster.firstname, " ",  prospectmaster.lastname) as Name, '
      + ' (CASE WHEN housemaster.estsettl_date IS NULL then date_format(DATE_ADD(DATE_ADD(LAST_DAY(housemaster.ratified_date), INTERVAL +1 DAY), INTERVAL +6 MONTH),"%m/%d/%Y") ELSE housemaster.estsettl_date END) AS ClosingDate, '
      + ' DATE_FORMAT(DATE_ADD(DATE_ADD(LAST_DAY(housemaster.ratified_date), INTERVAL +1 DAY), INTERVAL +6 MONTH) , "%Y") AS ClosingYear, '
      //+ ' (CASE WHEN (housemaster.companycode) = '001' AND (devmaster.developmentcode) = '34' THEN '003' ELSE housemaster.companycode END) AS Company_Code, '
      + ' devmaster.developmentcode, housemaster.lotnumber, elevationmaster.squarefootage as Plan, housemaster.elevationcode, devmaster.description as Community, housemaster.baseprice as BasePrice, '
      + ' housemaster.baseprice+housemaster.optionsprice+housemaster.lotpremium+ housemaster.promissoryamt1+ housemaster.promissoryamt2+ prospectmaster.otheramt3+ housemaster.upgradeprice as TotalPrice, '
      + ' prospectmaster.optiondeposit, " " AS ListDate, salespersonmast.salespersoncode AS Sales, CONCAT(salespersonmast.firstname, " ",  salespersonmast.lastname) as SalesPersonName, '
      + ' CONCAT(prospectfinance.loanofficer, "/", prospectfinance.lendercode) AS LenderBank, CONCAT(agentmaster.agentname, "/", brokermaster.brokername) AS RealtorBroker,      "" as PossibleCAN, housemaster.misc1_field AS Status,  DATE_FORMAT(housemaster.contract_date, "%m/%d/%Y") AS contract_date, '
      + ' DATE_FORMAT(housemaster.ratified_date, "%m/%d/%Y") AS ratified_date, '
      + ' DATE_FORMAT((CASE WHEN housemaster.settlement_date IS NULL then " " ELSE housemaster.settlement_date END), "%m/%d/%Y") as settlement_date '
 + ' FROM ((((((housemaster INNER JOIN prospectmaster ON (housemaster.developmentcode = prospectmaster.developmentcode) '
 + ' AND (housemaster.companycode = prospectmaster.companycode) '
 + ' AND (housemaster.housenumber = prospectmaster.housenumber)) INNER JOIN elevationmaster ON (housemaster.companycode = elevationmaster.companycode) AND (housemaster.developmentcode = elevationmaster. developmentcode) '
 + ' AND (housemaster.modelcode = elevationmaster.modelcode) AND (housemaster.elevationcode = elevationmaster.elevationcode)) INNER JOIN modelmaster ON (elevationmaster.modelcode = modelmaster.modelcode) '
 + ' AND (elevationmaster.companycode = modelmaster.companycode)) INNER JOIN devmaster ON (elevationmaster.developmentcode = devmaster.developmentcode) AND (elevationmaster.companycode = devmaster.companycode)) '
 + ' INNER JOIN companymaster ON (prospectmaster.companycode = companymaster.companycode) AND (prospectmaster.companycode = companymaster.companycode)) '
 + ' INNER JOIN salespersonmast ON (housemaster.companycode = salespersonmast.companycode) AND (housemaster.salesmancode = salespersonmast.salespersoncode)) '
 + ' LEFT JOIN prospectconting ON prospectmaster.casenumber = prospectconting.casenumber '
 + ' LEFT JOIN prospectfinance ON prospectmaster.casenumber = prospectfinance.casenumber '
 + ' LEFT JOIN agentmaster ON (prospectmaster.BROKERCODE = agentmaster.brokercode) AND (prospectmaster.AGENTCODE = agentmaster.agentcode) '
 + ' LEFT JOIN brokermaster ON agentmaster.brokercode = brokermaster.brokercode '
 + ' INNER JOIN udprospectmst ON (prospectmaster.casenumber = udprospectmst.casenumber) '
 + ' GROUP BY housemaster.companycode, housemaster.contract_date, housemaster.ratified_date, housemaster.settlement_date, housemaster.address1 '
 + ' HAVING ( housemaster.companycode="005" ) and housemaster.baseprice > 0 '
 + ' ORDER by housemaster.companycode, housemaster.address1);')
 
 var numCols = results.getMetaData().getColumnCount();
 
  
  var values = [];
  while (results.next()) {
    var temp = [];
      for (var col = 0; col < numCols; col++) {
      temp.push(results.getString(col + 1))
    }
    values.push(temp);
  }
  Logger.log(values)
 
  results.close();
  stmt.close();
    
  sheet.getRange('A1:X400').clearContent();
  
   
  // var time = new Date(time);
  // var now = new Date();
  // var settime = 'Last Updated: ' + now  
  // SpreadsheetApp.getActiveSheet().getRange('A4').setValue(settime);
  
  sheet.getRange(5, 1, values.length, values[0].length).setValues(values);
  results.close  //Edit: does this require ()?
 }

标签： mysqlgoogle-apps-scriptgoogle-sheetsgoogle-sheets-apigoogle-workspace

解决方案

我安装了 Google Tables API 并仔细查看了它的功能，您可以在 Workspaces and Tables 中获取和列出，但没有创建。因此，如果您无法创建表和工作区，您打算如何从数据库中创建表。

当前版本的表格是一个测试版，显然有计划在未来扩展它，但我不知道其中包括什么。

推荐阅读