title: Ovewrite table Google Script interactive Google Sheet to Google Docs and vice-versa
body: <p>This is my final project in view mode :</p>
  
  <p><a href="https://drive.google.com/open?id=1gOOb3hND3q2v0vTy8SfPVHFiCnTkJPR5" rel="nofollow noreferrer">https://drive.google.com/open?id=1gOOb3hND3q2v0vTy8SfPVHFiCnTkJPR5</a></p>
  
  <p>each row corresponds to a Google Doc project so when I modify in the row it will modify the corresponding Doc and when I click to see the document in the same window when I modify if then click the submit button I will change the row so I have an interactivity 100% with only the trigger onEdit(e) which will call function   Edit(e) and   openDialog(e).</p>
  
  <p>The problem is that it can overwrite the Google Doc file as in the image. The problem comes from function </p>
  
  <pre><code>onEdit(e){
    Edit(e);
    openDialog(e); // tick to see the project in the same window and modify it transfer to the row 
  }
  </code></pre>
  
  <p>if I put  openDialog(e); in comment if for instance I delete a text field in the row it will modify it correctly and with the openDialog(e) it will overwrite it. I've tried to put the openDialog code into the Edit(e) code as in the New.gs file but it's unsuccesful and close the doc  <a href="https://script.google.com/d/1bV_eJONvUAbHyO6OB04atfm_sb5ZO8LWNoQ23fxf0lFnRzHRSwW7hsQc/edit?usp=sharing" rel="nofollow noreferrer">https://script.google.com/d/1bV_eJONvUAbHyO6OB04atfm_sb5ZO8LWNoQ23fxf0lFnRzHRSwW7hsQc/edit?usp=sharing</a> Do you have an idea to solve it? Thank you very much :) <strong><em>Separately they function very well. Together no it can overwrte the file so I don't know I need to close the Google Doc at very time because I open it 2 times?</em></strong></p>
  
  <p>It's the column J; I set a trigger to view the Google Doc in the same window and I can modify inside submit button and will change the row (this is the openDialog corresponding part) 
  <a href="https://i.stack.imgur.com/JnnTi.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JnnTi.png" alt="enter image description here"></a>
  Edit : sorry this is the code </p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>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" ) &amp;&amp;   ( 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);
    }
  }</code></pre>
  <pre class="snippet-code-html lang-html prettyprint-override"><code>&lt;!DOCTYPE html&gt;
  &lt;html&gt;
  &lt;head&gt;
  &lt;base target="_top"&gt;
  &lt;/head&gt;
  &lt;body&gt;
  &lt;iframe id="srcFrame" src="&lt;?= body ?&gt;" name="&lt;?= insert ?&gt;" width="1000" height="400"&gt;&lt;/iframe&gt;
  &lt;input type="button" value="Sumit changes to the Spreadsheet" onclick="submitDoc()"&gt;
  &lt;script&gt;
  function submitDoc() {
  var url = document.getElementById("srcFrame").src;
  var insert = document.getElementById("srcFrame").name;
  google.script.run.submitDoc(url,insert);
  google.script.host.close();
  }
  &lt;/script&gt;
  &lt;/body&gt;
  &lt;/html&gt;</code></pre>
  </div>
  </div>
  </p>