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.
from Google Script Container boundscript onEdit shared it overwrites the file
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);
}
}
This 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