Skip to first unread message

Techno Peace

unread,
Jun 16, 2019, 8:31:15 PM6/16/19
to mitappinv...@googlegroups.com
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

unread,
Jun 17, 2019, 3:47:49 AM6/17/19
to MIT App Inventor Forum
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

unread,
Jun 17, 2019, 4:36:45 AM6/17/19
to MIT App Inventor Forum

Ekran Görüntüsü (565).png


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

unread,
Jun 17, 2019, 8:27:05 AM6/17/19
to MIT App Inventor Forum
Please show your whole script and the web component blocks that you use to send the delete request to the web app 

Techno Peace

unread,
Jun 17, 2019, 8:41:29 AM6/17/19
to MIT App Inventor Forum
I've sent the link including the blocks and scripts. It would be the project built by you before.
Message has been deleted

Techno Peace

unread,
Jun 18, 2019, 7:10:45 AM6/18/19
to MIT App Inventor Forum
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

unread,
Jun 18, 2019, 11:24:25 AM6/18/19
to MIT App Inventor Forum
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.


Here are the blocks in the app

blocksAddNumber.png


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.


webappupdate.png


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.


updatesheetfromAI2.aia

Techno Peace

unread,
Jun 18, 2019, 12:02:50 PM6/18/19
to MIT App Inventor Forum

Ekran Görüntüsü (566).png

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

unread,
Jun 18, 2019, 12:21:44 PM6/18/19
to MIT App Inventor Forum
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

unread,
Jun 18, 2019, 12:22:46 PM6/18/19
to MIT App Inventor Forum
and don't forget to republish your web app after making changes :)

Techno Peace

unread,
Jun 18, 2019, 12:37:57 PM6/18/19
to mitappinv...@googlegroups.com
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

unread,
Jun 18, 2019, 12:46:56 PM6/18/19
to MIT App Inventor Forum
"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

unread,
Jun 18, 2019, 12:58:19 PM6/18/19
to mitappinv...@googlegroups.com
Okey I've made the script bound: *REMOVED*
What is the other bound I will make?

TimAI2

unread,
Jun 18, 2019, 2:10:47 PM6/18/19
to MIT App Inventor Forum
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

unread,
Jun 18, 2019, 2:31:10 PM6/18/19
to MIT App Inventor Forum
Is there only one bound? The bound I made to spreadsheet didn't solve the error? 

TimAI2

unread,
Jun 18, 2019, 2:48:19 PM6/18/19
to MIT App Inventor Forum
Please, review my tutorial/example:
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

unread,
Jun 18, 2019, 2:53:31 PM6/18/19
to MIT App Inventor Forum

Ekran Görüntüsü (568).png

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

unread,
Jun 18, 2019, 2:57:51 PM6/18/19
to MIT App Inventor Forum
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

unread,
Jun 18, 2019, 5:24:53 PM6/18/19
to MIT App Inventor Forum
"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

unread,
Jun 18, 2019, 5:33:01 PM6/18/19
to MIT App Inventor Forum
"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:
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

unread,
Jun 18, 2019, 6:42:01 PM6/18/19
to MIT App Inventor Forum
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

unread,
Jun 18, 2019, 7:32:06 PM6/18/19
to MIT App Inventor Forum
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

unread,
Jun 19, 2019, 1:23:14 AM6/19/19
to mitappinv...@googlegroups.com
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.
DeletingImagesOnGoogleDrivev1.aia

TimAI2

unread,
Jun 19, 2019, 5:58:19 AM6/19/19
to MIT App Inventor Forum
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.

sheetaddsheet.png


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


removesection.png



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

unread,
Jun 19, 2019, 7:27:27 AM6/19/19
to mitappinv...@googlegroups.com
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

unread,
Jun 19, 2019, 7:39:01 AM6/19/19
to MIT App Inventor Forum
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

unread,
Jun 20, 2019, 5:18:38 PM6/20/19
to MIT App Inventor Forum
Hello again, I also want my app refresh the spreadsheet when I upload image. Could you help me for this? Thanks.

TimAI2

unread,
Jun 20, 2019, 5:26:10 PM6/20/19
to MIT App Inventor Forum
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

unread,
Jun 20, 2019, 5:28:31 PM6/20/19
to MIT App Inventor Forum
How can I use it? Please give me a way. My scripts are bound. 

TimAI2

unread,
Jun 20, 2019, 5:53:01 PM6/20/19
to MIT App Inventor Forum
Is the web app to upload images bound to the same spreadsheet as the one for deleting files ?

Techno Peace

unread,
Jun 20, 2019, 5:55:29 PM6/20/19
to MIT App Inventor Forum
Uploading image script is standalone. 

TimAI2

unread,
Jun 20, 2019, 6:07:47 PM6/20/19
to mitappinv...@googlegroups.com
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

unread,
Jun 20, 2019, 6:24:05 PM6/20/19
to MIT App Inventor Forum
there is syntax error on code. There is no } at the end. 

Techno Peace

unread,
Jun 21, 2019, 8:27:38 AM6/21/19
to MIT App Inventor Forum
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

unread,
Jun 21, 2019, 10:18:58 AM6/21/19
to MIT App Inventor Forum
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

unread,
Jun 21, 2019, 11:05:18 AM6/21/19
to MIT App Inventor Forum
That is very nice for me, please :)

Techno Peace

unread,
Jun 21, 2019, 12:51:03 PM6/21/19
to MIT App Inventor Forum
I ACHİEVED! Finally, the uploading image code refresh my spreadsheet. I make the code you sent bound and it works fine. 
Message has been deleted

TimAI2

unread,
Jun 21, 2019, 1:01:04 PM6/21/19
to mitappinv...@googlegroups.com
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 :)
uploadAndRefreshScript.txt
uploadImageGDRefreshSheet.aia
Reply all
Reply to author
Forward
Message has been deleted
Message has been deleted
Message has been deleted
Message has been deleted
This conversation is locked
You cannot reply and perform actions on locked conversations.
0 new messages