google-apps-script - 通过函数使用脚本创建文档

Question

有没有办法通过谷歌脚本函数创建谷歌文档（包含脚本！）？我有一个 Google 电子表格，其中有一个菜单点，可让我使用以下代码创建文档：

var row = SpreadsheetApp.getActiveSheet().getActiveCell().getRow()
var col = 3
var titletranslation = SpreadsheetApp.getActiveSheet().getRange(row, col).getValue()
var translation = Browser.inputBox(titletranslation , 'Please enter the translation here', Browser.Buttons.OK_CANCEL)
var ss = SpreadsheetApp.getActiveSpreadsheet()
doc = DocumentApp.create(translation);
SpreadsheetApp.getActiveSheet().getRange(row, 6).setValue(doc.getUrl())

现在我想构建，文档有自己的脚本，但我没有设法通过另一个脚本将脚本添加到创建的文档中。有谁知道这是否可能以及如何？

先感谢您！

标签： google-apps-scriptgoogle-sheets

Answer 1

为了实现最终目标，您必须设置几件事。我将列出它们，以便您可以逐步遵循它们：

1) 在您的 Apps 脚本项目中，转到View -> Show manifest file。

2）打开appscript.json出现的文件并粘贴：

{
  "timeZone": "Europe/Paris",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/script.projects",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/documents",
    "https://www.googleapis.com/auth/spreadsheets"
  ]
}

3) 到谷歌云平台复制项目号

4) 在您的 Apps Script 项目中，转到Resources -> Cloud Platform project，粘贴之前复制的项目编号，然后单击Set Project。

5）现在，使用我将为您提供的这段代码，我将解释它的功能：

该main函数将获取和设置工作表的值。请注意，我还在代码中添加了一个全局 JSON 变量来构建对Apps Script API的请求

// JSON for the requests 
var options = {
  'headers': { 'Authorization': `Bearer ${ScriptApp.getOAuthToken()}`},
  'contentType': 'application/json',
  'method': '',
  'payload': ''
};

// This is the main function to run 
function main() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveCell().getRow();
  var titleTranslation = sheet.getRange(row, 3).getValue();
  var translation = Browser.inputBox(titleTranslation , 'Please enter the translation here:', Browser.Buttons.OK_CANCEL);
  var docUrl = createDocwithCode(translation);
  sheet.getRange(row, 6).setValue(docUrl);  
}

这createDocwithCode将允许您创建将链接到您的文档的 Apps 脚本项目。

// This will create the doc and link an Apps Script project
function createDocwithCode(translation){
  var doc = DocumentApp.create(translation);
  // Make a POST request with a JSON payload.
  options['method'] = "post";
  options['payload'] = JSON.stringify({ 'title': doc.getName(), 'parentId': doc.getId()});
  // Call the Apps Script API -> Method: projects.create
  var res = UrlFetchApp.fetch('https://script.googleapis.com/v1/projects', options);
  addCodeToDoc(JSON.parse(res.getContentText()));
  return doc.getUrl();
}

将addCodeToDoc使用所需内容填充新创建的 Apps 脚本代码。

// Fill the previously Created Apps Script project with content 
function addCodeToDoc({scriptId, title}){
  // Make a PUT request with a JSON payload.
  options['method'] = "put";
  options['payload'] = JSON.stringify({ 
    "files": [
    {
      "name": title.replace(/\s+/g, ''),
      "type": "SERVER_JS",
      "source": createFunctionForDoc()
    },
    {
      "type": "JSON",
      "name": "appsscript",
      "source": "{\n  \"timeZone\": \"Europe/Paris\",\n  \"dependencies\": {\n  },\n  \"exceptionLogging\": \"STACKDRIVER\"\n}"
    } 
  ]
  })
  // Call the Apps Script API -> Method: projects.updateContent
  UrlFetchApp.fetch(`https://script.googleapis.com/v1/projects/${scriptId}/content`, options);
}

最后createFunctionForDoc是一个功能，可以轻松地在您新创建的 Apps 脚本代码中编写未来的代码。

// Fill The Script with your desired funtions                     
function createFunctionForDoc(){
    return `
      function customFunction(){
        Logger.log("Hello World");
      }
    `
}

您的整个代码将如下所示：

// JSON for the requests 
var options = {
  'headers': { 'Authorization': `Bearer ${ScriptApp.getOAuthToken()}`},
  'contentType': 'application/json',
  'method': '',
  'payload': ''
};

// This is the main function to run 
function main() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveCell().getRow();
  var titleTranslation = sheet.getRange(row, 3).getValue();
  var translation = Browser.inputBox(titleTranslation , 'Please enter the translation here:', Browser.Buttons.OK_CANCEL);
  var docUrl = createDocwithCode(translation);
  sheet.getRange(row, 6).setValue(docUrl);  
}


// This will create the doc and link an Apps Script project
function createDocwithCode(translation){
  var doc = DocumentApp.create(translation);
  // Make a POST request with a JSON payload.
  options['method'] = "post";
  options['payload'] = JSON.stringify({ 'title': doc.getName(), 'parentId': doc.getId()});
  // Call the Apps Script API -> Method: projects.create
  var res = UrlFetchApp.fetch('https://script.googleapis.com/v1/projects', options);
  addCodeToDoc(JSON.parse(res.getContentText()));
  return doc.getUrl();
}

// Fill the previously Created Apps Script project with content 
function addCodeToDoc({scriptId, title}){
  // Make a PUT request with a JSON payload.
  options['method'] = "put";
  options['payload'] = JSON.stringify({ 
    "files": [
    {
      "name": title.replace(/\s+/g, ''),
      "type": "SERVER_JS",
      "source": createFunctionForDoc()
    },
    {
      "type": "JSON",
      "name": "appsscript",
      "source": "{\n  \"timeZone\": \"Europe/Paris\",\n  \"dependencies\": {\n  },\n  \"exceptionLogging\": \"STACKDRIVER\"\n}"
    } 
  ]
  })
  // Call the Apps Script API -> Method: projects.updateContent
  UrlFetchApp.fetch(`https://script.googleapis.com/v1/projects/${scriptId}/content`, options);
}

// Fill The Script with your desired funtions                     
function createFunctionForDoc(){
    return `
      function customFunction(){
        Logger.log("Hello World");
      }
    `
}

文档

有我用来帮助你的文档

• Apps 脚本 API

• 方法：projects.create

• 方法：projects.updateContent

• 类 UrlFetchApp

• 启用脚本授权和访问

• https://developers.google.com/apps-script/concepts/scopes#setting_explicit_scopes