javascript - 将表 Google Script 交互式 Google Sheet 覆盖到 Google Docs，反之亦然

Question

这是我在视图模式下的最后一个项目：

https://drive.google.com/open?id=1gOOb3hND3q2v0vTy8SfPVHFiCnTkJPR5

每行对应一个 Google Doc 项目，所以当我在行中修改时，它将修改相应的 Doc，当我点击在同一个窗口中查看文档时，如果然后点击提交按钮，我将更改行，所以我有只有触发器 onEdit(e) 的交互性 100%，它将调用函数 Edit(e) 和 openDialog(e)。

问题是它可以覆盖图像中的 Google Doc 文件。问题来自功能

onEdit(e){
  Edit(e);
  openDialog(e); // tick to see the project in the same window and modify it transfer to the row 
}

如果我把 openDialog(e); 在评论中，例如，如果我删除行中的一个文本字段，它将正确修改它，并且使用 openDialog(e) 它将覆盖它。我尝试将 openDialog 代码放入 New.gs 文件中的 Edit(e) 代码中，但没有成功并关闭文档 https://script.google.com/d/1bV_eJONvUAbHyO6OB04atfm_sb5ZO8LWNoQ23fxf0lFnRzHRSwW7hsQc/edit?usp=sharing Do你有解决的办法吗？非常感谢 :)它们分别运行得很好。不，它可以覆盖文件，所以我不知道我需要关闭谷歌文档，因为我打开了 2 次？

是 J 列；我设置了一个触发器以在同一窗口中查看 Google Doc，我可以修改内部提交按钮并更改行（这是 openDialog 对应部分） 编辑：对不起，这是代码

var TITLE = 'Show Google Doc';
var SPREADSHEET_ID = "17ssKkCAoPUbqtT2CACamMQGyXSTkIANnK5CjbbZ1LZg"; // = assign your spreadsheet id to this variable


var column_name_project ;
var column_code_project ;
var column_chef_project;
var column_service ;
var column_other_services ;
var column_type_of_project ;
var column_perimeter ; 
var column_date_project ;

var COLUMN_URL ;
var COLUMN_VIEW_Google_Doc;


/** will return the number of the column correspondant **/
function find_columns_in_projet(){
 //search the columns
}

function onEdit(e){
  Edit(e);
 // openDialog(e);
}


function Edit(e) { 
  find_columns_in_projet();

  var tss_bis = SpreadsheetApp.openById(SPREADSHEET_ID);
  var sheet_bis = tss_bis.getSheets()[0];
  var numRows_bis = sheet_bis.getLastRow();
  var lastColumn_bis = sheet_bis.getLastColumn();
  
  //from the second line car the first line we have the headers
  var data_sheet = sheet_bis.getRange(1,1,numRows_bis,lastColumn_bis).getDisplayValues();
  //Access the range with your parameter e.
  var range = e.range;
  
  var row = range.getRow();
  var column = range.getColumn();
  if( e.range.getColumnIndex() != COLUMN_URL + 1 ) {
    var URL = data_sheet[row-1][COLUMN_URL-1];
    Logger.log('Le URL est bien : ' , URL);
    
    var body = DocumentApp.openByUrl(URL).getBody();
    Logger.log('The body is ' + body );
    if(body)
    {... code to write the information from the spreadsheet to the Google Doc 
   }   
}
}

/** every row in the current Sheet corresponds to a Google Doc Document   **/
/**  to see the Google Doc in the same page  click in colum J and be able to modify the Google Doc 8 rows table inside
By clicking the button Submit it will transfer the information with what you have changed to the row of the corresponding project int the Sheet  **/

function openDialog(e) {
  
  /**  columns in the Spreadsheet that are lines in the Google Doc table  **/
/**  find_columns_in_projet();
  
  /** the good Sheet  **/
  if( ( e.range.getSheet().getName() === "Sheet1" ) &&   ( e.range.getColumnIndex() == COLUMN_VIEW_Google_Doc ) ) {
    if( e.value === "TRUE" ) {
      try {
        //Get Google Doc body
        /**  the URL that is in the column I  **/
        
        var URL = e.range.offset(0,-1,1,1).getValue();
        e.range.setValue("FALSE");
        
        
        // Add this line
        var ui = HtmlService.createTemplateFromFile('ModeLessDialog');
        ui.body = URL;                                                                         // Pass url to template
        ui.insert = e.range.getRow() ;
        ui = ui.evaluate().setWidth(1000).setHeight(500);
        SpreadsheetApp.getUi().showModalDialog(ui, 'Show Google Doc');
      }
      catch(err) {
        Logger.log(err);
      }
    }
  }
}


function submitDoc(url,insert) {
  /** the Spreadsheet need for getRange insert position **/
  var tss_bis = SpreadsheetApp.getActiveSpreadsheet();
  var sheet_bis = tss_bis.getSheets()[0];
  find_columns_in_projet();
  try {
    Logger.log(url);
    var google_doc = DocumentApp.openByUrl(url) ;
    var body = google_doc.getBody();
    if(body) {
code to write the information from the Google Doc into the Spreadsheet by button submit
      }
      Logger.log(body);
    }
    return true;
  }
  catch(err) {
    Logger.log(err);
  }
}

<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<iframe id="srcFrame" src="<?= body ?>" name="<?= insert ?>" width="1000" height="400"></iframe>
<input type="button" value="Sumit changes to the Spreadsheet" onclick="submitDoc()">
<script>
function submitDoc() {
var url = document.getElementById("srcFrame").src;
var insert = document.getElementById("srcFrame").name;
google.script.run.submitDoc(url,insert);
google.script.host.close();
}
</script>
</body>
</html>

标签： javascriptgoogle-apps-scriptgoogle-sheets-apigoogle-docs-api