Friday, 3 August 2018

Google Script Container boundscript onEdit shared it overwrites the file

I have a container bound script inside a Google Spreadsheet. In fact every row corresponds to a Google Doc file and I've implemented 2 solutions: * if I modify a cell it will go to search the URL in the same row open the Google Doc table and modify it *In the Sheet I have a column J if I tick inside I am able to see the Google Doc file in a dialog box and I can modify it (the table inside) and press the Submit button and it will modify the row so I have a Google Spreadsheet Doc interaction and if works when it's only me on the project ^^
There is no problem with the script it's functioning when used by a sigle user it's a container bound script I will detail the steps:
I will try to better explain the behavior. There are 2 things I can do with this container-script bound to the Spreadsheet because each row in the Spreadsheet corresponds to a Google Doc file :
1) to modify a row it will search the URL column open the Google Doc file and modify it . To see if if modifies corectly I can open the URL to see or to tick to see the Google Doc in the same window so this is the exchange modify Spreadsheet to Google Doc
2) to modify a Google Doc I tick in the column J modify the 8 row table and then by pressing the Submit button I change the corresponding row so this is the exchange from Google Doc to the Spreadsheet
If I share the project with another user we overwrite the GDoc file I used the Lock service but still doesn't work .I hope I didn't forget anything.Any idea is welcomed .Thank you very much.
var TITLE = 'Show Google Doc';
var SPREADSHEET_ID = "1ooHkCYkcJYwc1Y0BBUkqeWHLTuSEfnRDNH4Bn-z_mxw"; // = 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;

function createSpreadsheetEditTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('solve')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

/** will return the number of the column correspondant >>>> Name project,code,chef,service,
 ...... etc .**/
function find_columns_in_projet(){
  var tss = SpreadsheetApp.openById(SPREADSHEET_ID);
  var sheet = tss.getSheets()[0];
  var lastColumn = sheet.getLastColumn();
  var data = sheet.getRange(1,1,1,lastColumn).getValues()[0]; 

  for(var n=0;n<data.length;n++){ 

    if(data[n].toString().indexOf('Name project') > -1)
      column_name_project = n+1;


    else  if(data[n].toString().indexOf('Code') > -1)
      column_code_project = n + 1;

    else if(data[n].toString().indexOf('Chef') > -1)
      column_chef_project= n + 1;

    else  if(data[n].toString().indexOf('Service') > -1)
      column_service = n +1 ;

    else if(data[n].toString().indexOf('Other services') > -1)
      column_other_services = n + 1;

    else if(data[n].toString().indexOf('Type of the project') > -1)
      column_type_of_project = n + 1;

    else if(data[n].toString().indexOf('Perimeter') > -1)
      column_perimeter = n + 1;

    else if(data[n].toString().indexOf('Date') > -1)
      column_date_project = n + 1;

    else if(data[n].toString().indexOf('URL') > -1)
      COLUMN_URL = n +1  ;

    else if(data[n].toString().indexOf('View project same window') > -1)
      COLUMN_VIEW_Google_Doc = n +1  ;
  }
}



function solve(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 lock = LockService.getPublicLock();///added this here
    lock.waitLock(3000);   ///added this here
    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)
    {
      var ok = 0;                                    //for the moment we don't have the 
table to modify the values we've put in the spreadsheet 
      var numChildren=body.getNumChildren();
      var compteur=0;
      //while we don't find the table we will search
      while(ok ==0 && compteur<numChildren)
      {
        var child=body.getChild(compteur);
        /** =========We are concerned by the first table with at least 8 rows ===**/
        Logger.log('the type in the loop  ' + child.getType());

        //here is our table  **/
        if(child.getType()==DocumentApp.ElementType.TABLE && child.asTable().getNumRows() 
>= 8)
        {
          //so the variable gets 1 >>  ok = 1
          ok=1;   

          /**  The number of rows in the Google Doc table **/
          var numrows = child.asTable().getNumRows();
          Logger.log('The number of rows is   ' + numrows);
          Logger.log('The new date is ' + data_sheet[row-1][column_date_project-1]);
          /** we will loop in the table **/ 

          var k = 1;     //we know the information is at right so we don't loop we will 
replace the value 
          /**  is working   **********************************************/
          /** put the value of data_seet into the Google Doc at the corresponding place  **/
          child.asTable().getCell(0, k).editAsText().setText( data_sheet[row-1][column_name
_project - 1] )  ;
          child.asTable().getCell(1, k).editAsText().setText( data_sheet[row-1][column_code
_project - 1] )  ;
          child.asTable().getCell(2, k).editAsText().setText( data_sheet[row-1][column_che
f_project - 1] )  ;
          child.asTable().getCell(3, k).editAsText().setText( data_sheet[row-1][column_service - 1] )  ;
          child.asTable().getCell(4, k).editAsText().setText( data_sheet[row-1][column_oth
er_services - 1] )  ;
          child.asTable().getCell(5, k).editAsText().setText( data_sheet[row-1][column_type_of_project - 1] )  ;
          child.asTable().getCell(6, k).editAsText().setText( data_sheet[row-1][column_pe
rimeter - 1] )  ;
          child.asTable().getCell(7, k).editAsText().setText( data_sheet[row-1][column_d
ate_project-1] )  ;
        }
        compteur++;       /** until we find our table **/
      }
    }  lock.releaseLock();  ///and finish here
  } else   if( e.range.getColumnIndex() == COLUMN_URL + 1 ) {
    var lock = LockService.getPublicLock();///added this here
    lock.waitLock(30000);   ///added this here
    /** the good Sheet  **/
    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');
        lock.releaseLock();  ///and finish here
      }
      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];
  var lock = LockService.getPublicLock();///added this here
  lock.waitLock(30000);   ///added this here
  find_columns_in_projet();
  try {
    Logger.log(url);
    var google_doc = DocumentApp.openByUrl(url) ;
    var body = google_doc.getBody();
    if(body) {
      // Put your doc search code here
      var ok = 0;                                    //ok =0 for the moment we don't 
have a 8-row table we are interested in to transfer in the table 
      var numChildren=body.getNumChildren();
      var compteur=0;
      //while we didn't find the table we loop
      while(ok ==0 && compteur<numChildren)
      {
        var child=body.getChild(compteur);
        /** ============we are concerned by the first table with at least 8 rows 
 ================**/

        //we find our table in which we are interested in to get the information from 
the second colum of this table and put them in the row of the spreadsheet  **/
        if(child.getType()==DocumentApp.ElementType.TABLE && child.asTable().getNumRows() 
>= 8)
        {
          //we found our first table so >>>>  ok = 1
          ok=1;   
          /** we will get the information we are interested in  **/ 

          var k = 1;     //we know the information is at the right(at left there are the 
name project,date,chef we are not interested to get this info as we already have this in 
the column of the spreadsheet 
          var celltxt,trange;


          /** we will put the celltxt(data from the Google Doc table at the right) in
 the spreadsheet at the position insertion_position and at the corresponding column +
 autoResizeColumn **/
          /** for the name of the project **/
          celltxt=child.asTable().getCell(0, k).editAsText().getText();
          trange = sheet_bis.getRange(insert, column_name_project);           
//the place to put in the spreadsheet 
          trange.setValue(celltxt);

          /** for the code of the project **/
          celltxt=child.asTable().getCell(1, k).editAsText().getText();
          trange = sheet_bis.getRange(insert, column_code_project);            
//the place to put in the spreadsheet 
          trange.setValue(celltxt);

          /** for the chef of the project **/
          celltxt=child.asTable().getCell(2, k).editAsText().getText();
          trange = sheet_bis.getRange(insert, column_chef_project);           
//the place to put in the spreadsheet 
          trange.setValue(celltxt);

          /** for the service of the project **/
          celltxt=child.asTable().getCell(3, k).editAsText().getText();
          trange = sheet_bis.getRange(insert, column_service);               
//the place to put in the spreadsheet 
          trange.setValue(celltxt);

          /** for the other services of the project **/
          celltxt=child.asTable().getCell(4, k).editAsText().getText();
          trange = sheet_bis.getRange(insert, column_other_services);       
//the place to put in the spreadsheet 
          trange.setValue(celltxt);

          /** for the type of the project **/
          celltxt=child.asTable().getCell(5, k).editAsText().getText();
          trange = sheet_bis.getRange(insert, column_type_of_project);        
//the place to put in the spreadsheet 
          trange.setValue(celltxt);

          /** for the perimeter of the project **/
          celltxt=child.asTable().getCell(6, k).editAsText().getText();
          trange = sheet_bis.getRange(insert, column_perimeter);             
//the place to put in the spreadsheet 
          trange.setValue(celltxt);

          /** for the date of the project **/
          celltxt=child.asTable().getCell(7, k).editAsText().getText();
          trange = sheet_bis.getRange(insert, column_date_project);             
//the place to put in the spreadsheet 
          trange.setValue(celltxt);
        }
        compteur++;       /** loop until we find our table **/
      }
      Logger.log(body);
    }
    return true;
    lock.releaseLock();  ///and finish here
  }

  catch(err) {
    Logger.log(err);
  }
}

enter image description hereThis is the link with my project in view mode because there are anonymous persons who tried to modify the columns etc . it's a container bound script https://drive.google.com/open?id=1gOOb3hND3q2v0vTy8SfPVHFiCnTkJPR5


from Google Script Container boundscript onEdit shared it overwrites the file

No comments:

Post a Comment