Waiting and stop waiting until changes a row in google sheets. How to do?

[PROGETTAZIONE STUDIO GB]

Hy,

I'm working for a drive's  file and folder (including subfolders) management system to render in a google sheets. I almost ended the code and here you have the copy of it in a beautiful bootstrap interface: https://docs.google.com/spreadsheets/d/1lNhNNRhX8aQTxt8QWfCSfD5K_4AK2wRxgoK2D2G4jF0/edit?usp=sharing

Reloading files and folders in a google sheet may be very slow. So I'm building a code to wait until the html reload after google sheet is entirely  reloaded.

I find the condition to detect that is : the page reloaded have 1 row more that the prevoius one (below you can see the core code)

So please can you help me to find the code to reload the html table page when the sheet is entirely reloaded after I upload another file?

Here the core code (server side):

To load the Table

function getTableData() {

//var ss=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("table");

var ss=SpreadsheetApp.getActiveSheet();

var firstRange=ss.getRange(2,1, ss.getLastRow()-1, 6);

//Logger.log(ss);

var data=ss.getRange(2,1, ss.getLastRow()-1, 6).getValues();

// Logger.log(data);

SpreadsheetApp.flush();

cambiaformato();

var rowsForFirstRange=heightRange(firstRange);

var rowsForLastRange=rowsForFirstRange+1;

Logger.log(rowsForFirstRange);

Logger.log(rowsForLastRange);

//if (heightRange(firstRange)==rowsForLastRange) {

return data;

//}

//else {

Logger.log(rowsForFirstRange);

//}

}

The condition (read the height in rows of the range: when the page is loaded with another file the height of the range is 1 row longer)

function heightRange () {

var ss=SpreadsheetApp.getActiveSheet();

var range=ss.getRange(2,1, ss.getLastRow()-1, 6);

return range.getHeight();

//Logger.log(range.getHeight());

}

Many Thanks

Mauro

[Alan Wells]

Have you tried:

sheet.appendRow(subfolderdata);

SpreadsheetApp.flush();

[PROGETTAZIONE STUDIO GB]

Yes but this not resolves the problem. In fact if you run the web form, load a file and wait for a while after uploading in g-drive, you will see in the sheet the file is loading and if you refreshe after a while the web page works perfectly!

The problem here is to refresh automatically the page after this exact sequence: upload the file in g-drive, charge in g-sheet sheet, refresh html page after complete g-sheet sheet is chaged (this last point is my problem!)

[Alan Wells]

The flow of the code should be as follows:

• Call server from client side (HTML script tag) and get all file ID's by category of folder ID
• Send all folder and file ID's back to the client side
• Loop through all folders and file ID's on the client side, calling the server once for each file ID.
• Use recursion - meaning, that the client side loop code will call itself in the success handler
• Verify that the server side code completed by sending the file ID BACK to the client side at the end of the server code.

So, your code needs to be restructured.

You'll still have basically the same "while" loops in your server code to get the folder and file ID's, but that is just for collecting the file ID's.

Don't use the server side "while" loops to do the actual setting of Sheet values.

So, remove the "append" line from the server side loop.

You will need a new function to append the line to the Sheet and flush the sheet, but that won't be in the server code loop,

it will be called from the client side code.

So, the overview of this process is:

• Get all the folder and file ID at once with server code
• Send all folder and file ID's back to client
• Loop through folder and file ID's on the client side, NOT on the server side
• For every file ID only make one call to the server from the client side
• The client side code will automatically wait until THAT ONE file ID is processed.  No need for a "promise" or any other waiting strategy.
• The success handler for the google.script.run call will call the SAME FUNCTION that the google.script.run call is in.  This will continue the loop until there are no more file ID's left to process.
• In order to keep track of what file ID's are left to process, you can have an array that removes one file ID from the array for each call, and when all file ID's have been processed from the client side, there will be no more elements (File ID's) in the array.

The key to this, is that the successHandler will wait until the server code completes, and you are only processing one file ID at a time from the client side.  This guarantees that you won't get concurrent code instances running at the same time.  The google.script.run call in the client side function must be the last line of code in the client side function.   The client side code will wait for the successHandler but it won't wait for any lines of code after the google.script.run call.  So the, google.script.run call must be the "end of the line."  There can not be any code after that google.script.run call that can run.  You must restrict the code to running again through the successHandler.

If you try to use a promise in the client side code, then it gets complicated and "tricky."