Why Web App will Then Refresh the Google Sheet

[Techno Peace]

Hello! I'm building this project: HOWTO: Use Ai2 to Delete Images (or other files) on Google Drive And everything is okey excluding the sixth mission: The webapp will then delete the file and refresh the sheet. Web app deleted the file but didn't refresh the sheet. Why? Thank you. (The head must be Why will web app then not refresh the google sheet?)

[TimAI2]

Did you leave this part in the script? :

//This section just for the example - to allow images to be replaced from trash

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheets()[1];

  sheet.appendRow([e.parameter.id]);

  //end of example section

This will put the file back from "Trash" before refreshing the spreadsheet

[Techno Peace]

I suppose that the disability to refresh sheet is due to the error on apps script in 'parameter' sentence. I shared the error message before: 

[TimAI2]

Please show your whole script and the web component blocks that you use to send the delete request to the web app 

[Techno Peace]

I've sent the link including the blocks and scripts. It would be the project built by you before.

[Techno Peace]

The issue is related to that the apps script aren't executed. Cause, if I manually run the script, spreadsheet is refreshed. Could you control the issue here please:  HOWTO: Use Ai2 to Delete Images (or other files) on Google Drive . What can I do? I made the script bound. But I did'nt use the script link. I must? Thanks for your help.

[TimAI2]

OK, I am providing you with a working example of how to run a webapp from AI2 that updates a google sheet, providing you with all the assets and links for you to see how it all works.

The aia for the app is attached

Workflow

1. In the app, a user clicks on the Add Number button
2. This calls the web app, which adds 1 to the number currently in cell A1 of the google sheet
3. The webapp also returns the new number to Ai2
4. The app gets the response content from the webapp, and displays the new number

Here is a link to a gif showing this in action, you can see the app button click, the spreadsheet updating, and then the app updating, I ran it 4 times.

https://drive.google.com/open?id=16_Cg1LbPbP5qW8F4G3smHTLi1GEdQEzi

Here are the blocks in the app

Here is the code for the web app, which is a script bound to the spreadsheet

//for AI2 app

function doGet() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheets()[0];

  var rngVal = sheet.getRange("A1").getValue();

  var newVal = rngVal + 1;

  sheet.getRange("A1").setValue(newVal);

  return ContentService.createTextOutput(newVal);

}

//for PC browser

function doPost() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheets()[0];

  var rngVal = sheet.getRange("A1").getValue();

  var newVal = rngVal + 1;

  sheet.getRange("A1").setValue(newVal);

  return ContentService.createTextOutput(newVal);

}

When you publish the script as a web app, you should run it as you, but ensure that anyone can access it. Every time you make a change to the web app you must publish a new version.

You can also run the web app in your browser using the web app url, it will return a white page with the latest number in the top left corner:

https://script.google.com/macros/s/AKfycbz9XZ1YU_yaSsiJNqbk41p58b5ZgQpbrQ_aBfGPlmG5x8UId6m3/exec

Here is the link for the spreadsheet, so you can see the sheet updating as you activate the web app:

https://docs.google.com/spreadsheets/d/1YbuuoptAWtj4O9eIzipUzSRj5uvauJAqx5SSxjWq60w/edit#gid=0

You can see that this all works together, as long as you have all the pieces in the right place, and are running the latest updated versions of everything, it will work.

If you used a web app that required parameters, obviously your script would be different, but the same approach applies.

[Techno Peace]

But the run function doesn't have doGet execution in my project. How can I run it? The url finishing with "exec" didn't run it. 

[TimAI2]

Well there is your problem then. If you had shown me your full script as previously asked, we would have seen that you did not have a doGet().

All web apps must have a doGet() (and/or doPost()) in order to work

Run your function from the doGet() function and it should work.

[TimAI2]

and don't forget to republish your web app after making changes :)

[Techno Peace]

I struggled to build the project: HOWTO: Use Ai2 to Delete Images (or other files) on Google Drive ever since morning. I've built many web app project. But it is different. I failed all time. Finally, everything is okey, but the google script: *REMOVED*

even is executed manually. But not executed in app inventor with web.get function. I change function name to "doGet" but it didn't change anything. 

[TimAI2]

"I've built many web app project" !!

Looking at your files (finally a link!):

1. Not a bound script in the spreadsheet

2. All that will do is refresh what is there.

For the Delete Workflow (my example), there should be two bound projects, one for the web app and one to run the update sheet script. They do not interact with each other. 

Go back and look at my tutorial/example.

[Techno Peace]

Okey I've made the script bound: *REMOVED*

What is the other bound I will make?

[TimAI2]

The web app - it is not vital, you can have standalone or bound, but having it bound to the spreadsheet makes it easier to find and work with

[Techno Peace]

Is there only one bound? The bound I made to spreadsheet didn't solve the error? 

[TimAI2]

Please, review my tutorial/example:

HOWTO: Use Ai2 to Delete Images (or other files) on Google Drive

start from the beginning and compare each step until everything matches

You have two script projects bound to the spreadsheet

One is to fetch details about the files in a folder (this is just a script with function/s)

The other is the web app that you connect the AI2 app with (this is the web app you publish)

It is slightly confusing because they both contain the same function to list the files in the folder, but they are separate and different (the web app has the doGet() and do post() ).

[Techno Peace]

The blocks attached is too complicated. I think that one ID is enough to connect spreadsheet and I tried it. Can you explain why you use two IDs please?

[Techno Peace]

I check the tutorial many times, But the tutorial doesn't explain how to make script bound. Could you explain how I can? I could make one script bound. How can I make the other script bound? Thanks.

[TimAI2]

"The blocks attached is too complicated. I think that one ID is enough to connect spreadsheet and I tried it. Can you explain why you use two IDs please?"

This is to allow for using a different sheet on the spreadsheet from just the first one (which would be used by default) even though in the example the first sheet with gid=0 is used

[TimAI2]

"I check the tutorial many times, But the tutorial doesn't explain how to make script bound. Could you explain how I can? I could make one script bound. How can I make the other script bound? Thanks."

This is basic google apps script stuff !

1. Open your spreadsheet
2. Tools > Script Editor - this will open a new project
3. Create your first bound script project

To start a new script project also bound to the same spreadsheet

1. Open your spreadsheet
2. Tools > Script Editor - this will open the first project
3. In the Google Apps Script Editor:
4. File > New > Project
5. Create your next bound script project

Both these project files are bound to the spreadsheet.

The next time you open the spreadsheet and then go to Tools > Script Editor, a page will open to offer you the choice of which project you want to open

If you have created a standalone script and want it to be bound, do as above then copy your code across into the bound project file.

Further reading:

https://developers.google.com/apps-script/guides/bound

which also explains some of the benefits of using a container/bound script, other than those I have already mentioned along the way

[Techno Peace]

I make two scripts bound to spread sheet. And unfortunately same things again. Please help me to build this project:  HOWTO: Use Ai2 to Delete Images (or other files) on Google Drive . I spent twelve hours to build it. But I couldn't. :( I did whatever said in the tutorial and what you said. But there is something wrong I've never noticed. Could you check my building I did for me , please??

[TimAI2]

Please share your aia project file here, also please share your web app script in full, the link to the webapp (ends in exec) and the link to the spreadsheet. it is otherwise difficult to debug because i cannot directly access/run your project files.

[Techno Peace]

Thank you very much. 

Bound script web app: 

In full:                              *REMOVED*

ends in exec:                  *REMOVED*

Next bound script web app:

In full:                               *REMOVED*

ends in exec:                   *REMOVED*

Spreadsheet in full:      *REMOVED*

Aia file attached.

[TimAI2]

OK I can see where your problem was, your script should work now.

This is for the web app "deleteproject2"

I mentioned in a previous post that you needed to remove the section for untrashing an image (which was just for my example). You did not do this. This part needed a second sheet on the spreadsheet. You had not added a second sheet therefore the script was failing. I added a second sheet to your spreadsheet then the script worked OK.

For your purposes, you should remove the code section from your script, then you do not need the second sheet, unless you want a record of the files deleted on the second sheet

Your first script project "deleteproject" does not need to be published as a web app for the delete workflow to work.

Hopefully you will now be up and running.

I will add some extra documentation to my example to make it crystal clear what to do.

[Techno Peace]

Thanks a lot... again and again. You rescued me from big thing. You volunteers are wonderful. If there is something I can do for you, just enough to say. 

One unimportant thing that I edited my url post, anyone can see my links now?

[TimAI2]

You should be able to set the script projects and spreadsheet to private again. The web app will still work because it will be owned by your google account but can be accessed by anyone.

I have removed you links for you

[Techno Peace]

Hello again, I also want my app refresh the spreadsheet when I upload image. Could you help me for this? Thanks.

[TimAI2]

Use the same function: getFileIDsAndNames() in the web app used to upload the image file.

If your web app is standalone you will need to specify the spreadsheet ID

[Techno Peace]

How can I use it? Please give me a way. My scripts are bound. 

[TimAI2]

Is the web app to upload images bound to the same spreadsheet as the one for deleting files ?

[Techno Peace]

Uploading image script is standalone. 

[TimAI2]

In which case, modify your web app for image uploading like this:

function doGet(e) {

return message("Error: no parameters in doGet");

 

}

function doPost(e) {

if (!e.parameters.filename || !e.parameters.file || !e.parameters.imageformat) {

  return message("Error: Bad parameters in doPost");

} else {

  var imgf = e.parameters.imageformat[0].toUpperCase();

  var mime =

      (imgf == 'BMP')  ? MimeType.BMP

    : (imgf == 'GIF')  ? MimeType.GIF

    : (imgf == 'JPEG') ? MimeType.JPEG

    : (imgf == 'JPG')  ? MimeType.JPEG

    : (imgf == 'PNG')  ? MimeType.PNG

    : (imgf == 'SVG')  ? MimeType.SVG

    : false;

  if (mime) {

    var data = Utilities.base64Decode(e.parameters.file, Utilities.Charset.UTF_8);

    var blob = Utilities.newBlob(data, mime, e.parameters.filename);

    DriveApp.getFolderById('FOLDER ID HERE').createFile(blob);

     getFileIdAndNames();

     return message("Success");

  } else {

    return message("Error: Bad image format");

  }

}

}

function message(msg) {

return ContentService.createTextOutput(JSON.stringify({Result: msg })).setMimeType(ContentService.MimeType.JSON);

}

function getFileIdAndNames() {

 var FileFolderID = 'ENTER YOUR IMAGES FOLDER ID HERE'; // the ID of the folder with files

var ss = SpreadsheetApp.openById('ENTER YOUR SPREADSHEET ID HERE');

var sheet = ss.getSheets()[0];

var urlCol = 1;

var urlRow = 1;

var folder = DriveApp.getFolderById(FileFolderID);

var files = folder.getFiles();

 sheet.clear();

 while (files.hasNext()) {

var file = files.next();

 

  if (file.getName().indexOf(".jpg") != -1)  {

    sheet.getRange(urlRow, urlCol).setValue(file.getName());

    sheet.getRange(urlRow, urlCol+1).setValue(file.getId());

    sheet.getRange(urlRow, urlCol+2).setValue('https://drive.google.com/uc?export=view&id='+ file.getId());

    sheet.getRange(urlRow, urlCol+3).setValue('JPG');

    sheet.getRange(urlRow, urlCol+4).setValue(Math.round(file.getSize()/1000) + " KB");

    sheet.getRange(urlRow, urlCol+5).setValue(file.getName().slice(0, -4));

  } else if (file.getName().indexOf(".png") != -1) {

    sheet.getRange(urlRow, urlCol).setValue(file.getName());

    sheet.getRange(urlRow, urlCol+1).setValue(file.getId());

    sheet.getRange(urlRow, urlCol+2).setValue('https://drive.google.com/uc?export=view&id='+ file.getId());

    sheet.getRange(urlRow, urlCol+3).setValue('PNG');

    sheet.getRange(urlRow, urlCol+4).setValue(Math.round(file.getSize()/1000) + " KB");

    sheet.getRange(urlRow, urlCol+5).setValue(file.getName().slice(0, -4));

  }

   

urlRow = urlRow+1;

}

 sheet.sort(1, true);

 sheet.sort(4, true);

 sheet.insertRows(1);

 var title = [["Filename","File ID","Direct Link to View","File Type","File Size (KB)","Description"]];

 var fontStyles = [ [ "bold", "bold", "bold", "bold", "bold","bold" ]];

 var titleRange = sheet.getRange("A1:F1");

 titleRange.setValues(title);

 titleRange.setFontWeights(fontStyles);

}

adding the ID's for FOLDERS and SPREADSHEETS where indicated.

Save, and republish the webapp with a new version

With any luck, the spreadsheet should refresh when a new image is added

[Techno Peace]

there is syntax error on code. There is no } at the end. 

[Techno Peace]

I indicated the ID's, save it and republished with new version. (If creating new version is that file -> manage version -> save new version) But the script allow to upload image but not refresh spreadsheet. By the way, the script code you sent looks okey in my mail, but it looks weird in google group. And If I run manually getFieldAndImage() function, the function works fine. But I think the code can't see the function. 

[TimAI2]

I didn't test it, real world issues getting in the way ;)

Perhaps I will get a chance to look at this later.

[Techno Peace]

That is very nice for me, please :)

[Techno Peace]

I ACHİEVED! Finally, the uploading image code refresh my spreadsheet. I make the code you sent bound and it works fine. 

[TimAI2]

Aha - just got a chance to test and yes this does work.

Tagged this onto the back of the delete Images example and used the original upload Image aia

aia attached (after edit) along with a text file of the script required (too long for google groups....)

Right, that is enough "off topic/forum" work, use stackoverflow for google apps scripting questions :)