How to make a link between an excel file and AppInventor

[TIMAI2]

Yes, that is my scripting as well

Probably best if I build up a simple example. Need a little while to do that…

[ABG]

@Paozieu

I just finished making half the spreadsheet in appinventor with AGB’s method and it is very, very slow.

Did you build the .apk, or just run it in the Companion?

[TIMAI2]

Here is my Simple Read / Write example, for Ai2 and a Google Sheet.

SHEET
We set the google sheet up with two sheets (tabs), and the google apps script web app is bound to the spreadsheet (created by selecting Script Editor from the Tools Menu)

Sheet1 can be considered to be the workings sheet, with the calculations.
Sheet2 is just there to receive the data from AI2 (which is then transferred to Sheet1 by formulas)

The web app uses appendRow to pass the data into the google sheet on Sheet2. You will see that on each run of the “WRITE” part of the script it clears the contents of Sheet2. Nothing else should happen on this sheet apart from this activity.

On Sheet1, Column A receives the values from Sheet2 by direct formula, e.g Sheet1!A2 contains the formula =Sheet2!A1 and Sheet1!A3 contains the formula =Sheet2!B1, and so on. These formulas need to be left alone!

Once Sheet2 gets new data, Sheet1 receives the data and can perform the calculations set, in this instance, in Column C, rows C2:C6. This range is used for AI2 to read the output data back into the app.

WEB APP
The google apps script web app handles the supply of new data and returns the calculations, depending on the contents of the “func” parameter. I included an option for the output, by default it is set as a csv row, but it can be set to a json / javascript array if preferred. The script uses doGet(e), so it can be tested in a computer browser using the script url and parameters if required. (I have not used the new V8 javascript setting for this project). When creating the apps script web app, if you make ANY changes, remember to republish the app to a new version.

function doGet(e) {
  
  // ### Write INPUT to A1 to E1 on Sheet2 ###
  if(e.parameter.func == "WRITE") {
    var ss = SpreadsheetApp.getActive();
    var sh = ss. getSheetByName("Sheet2");
    sh.clearContents();
    sh.appendRow([e.parameter.a1,e.parameter.a2,e.parameter.a3,e.parameter.a4,e.parameter.a5]);
    return ContentService.createTextOutput("Data Successfully Written"); 
    }
  
  // ### Read OUTPUT from C2 to C6 on Sheet1 ###
  else if(e.parameter.func == "READ") {
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheetByName("Sheet1");
    var rg = sh.getRange("C2:C6").getValues();
    var outString = [];
      for(var row=0; row<5 ; ++row){
      outString.push(rg[row]);
      }
    
    return ContentService.createTextOutput(outString);                     // will return a1,a2,a3,a4,a5
    
    //return ContentService.createTextOutput(JSON.stringify(outString));   // will return [[a1],[a2],[a3],[a4],[a5]]
  }
   
}

The AI2 app is fairly straightforward, the user inputs values to five textboxes and presses WRITE, and receives a notification that the data was successfully written. If they press READ, then the calculated data is returned in a label.

SCREEN

BLOCKS

AIA

SimpleReadWrite.aia (3.4 KB)

SPREADSHEET

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

[Paozieu]

@ABG I tried both, it was very slow either way.

@TIMAI2 Thank you so much for your help !

I am having trouble changing your app to read my spreadsheet.

Here is what I get when I press the button write :

Here is the modified version of your app :

Blocks

Script

function doGet(e) {

// ### Write INPUT to A1 to E6 on Sheet2 ###
if(e.parameter.func == "WRITE") {
var ss = SpreadsheetApp.getActive();
var sh = ss. getSheetByName("Sheet2");
sh.clearContents();
sh.appendRow([e.parameter.a1,e.parameter.a2,e.parameter.a3,e.parameter.a4,e.parameter.a5,e.parameter.a6]);
return ContentService.createTextOutput("Data Successfully Written");
}

// ### Read OUTPUT from B1 to B6 on Sheet1 ###
else if(e.parameter.func == "READ") {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName("Sheet1");
var rg = sh.getRange("B1:B6").getValues();
var outString = ;
for(var row=0; row<5 ; ++row){
outString.push(rg[row]);
}

return ContentService.createTextOutput(outString);                     // will return a1,a2,a3,a4,a5

//return ContentService.createTextOutput(JSON.stringify(outString));   // will return [[a1],[a2],[a3],[a4],[a5]]

}

}

Spreadsheet

https://docs.google.com/spreadsheets/d/1FH1nI6DAeMTnsynb4ntIpYSIKoClQWu5-AfoHUdHhak/edit#gid=532133169

AIA

SimpleReadWrite_copy.aia (3.6 KB)

Edit : I tried to redo the same spreadsheet as yours and I end up with the same problem.

[TIMAI2]

A few things:

1. In your READ part of the script, change
   "var rg = sh.getRange(“B1:B6”).getValues();"
   to
   var rg = sh.getRange(“B2:B7”).getValues();

2. Although it is not presenting as an issue, it may be sensible to ad the uriEncode block to each of the textboxes like so:

3. There is a permissions issue with your script.
   a) You will need to republish and create a new version because of the changes above anyway
   b) make sure the script is published to be run as you (your gmail account), with anyone even anonymous can access. - you should be asked to give permissions, if not already.

If tested in a computer browser, everything works OK:

https://script.google.com/macros/s/AKfycbwJJt1WIFN6txR9RBvIqCft4keX4hJvMRdfKdpTBBaCWN5efio/exec?func=READ

https://script.google.com/macros/s/AKfycbwJJt1WIFN6txR9RBvIqCft4keX4hJvMRdfKdpTBBaCWN5efio/exec?func=WRITE&a1=175&a2=40&a3=-185&a4=95&a5=150&a6=-25

[Paozieu]

I was able to make it run this time. Thank you.

I just ran into an other problem.

When I try to run the functions READ and WRITE one after the other, I get a weird response. At the first click on btnXminus, the app inventor app reads nothing from the script then sometimes the response content of Web1.GotText from procedure READ will go into the list of WRITE randomly.

I feel like the WRITE procedure is being processed while the procedure READ is still being done.

Is there a way to “wait” until the READ procedure is finished before starting the WRITE procedure ?

Or maybe it’s an other problem.

Block concerned :

Rest of the blocks

.AIA

AI2_googlesheet_communication (1).aia (9.2 KB)

Script

 function doGet(e) {
  
  // ### Write INPUT to A1 to F1 on Sheet2 ###
  // ### Read OUTPUT from B2 to B7 on Sheet ###
  if(e.parameter.func == "WRITE") {
    var ss = SpreadsheetApp.getActive();
    var sh = ss. getSheetByName("Sheet2");
    sh.clearContents();
    sh.appendRow([e.parameter.a1,e.parameter.a2,e.parameter.a3,e.parameter.a4,e.parameter.a5,e.parameter.a6]);
    var sh = ss.getSheetByName("Sheet1");
    var rg = sh.getRange("B2:B7").getValues();
    var outString = [];
      for(var row=0; row<6 ; ++row){
      outString.push(rg[row]);
      }
    return ContentService.createTextOutput(outString); 
    }
  
  // ### Read OUTPUT from A2 to A7 on Sheet1 ###
  else if(e.parameter.func == "READ") {
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheetByName("Sheet1");
    var rg = sh.getRange("A2:A7").getValues();
    var outString = [];
      for(var row=0; row<6 ; ++row){
      outString.push(rg[row]);
      }
    
    return ContentService.createTextOutput(outString);                     // will return a1,a2,a3,a4,a5,a6
    
    //return ContentService.createTextOutput(JSON.stringify(outString));   // will return [[a1],[a2],[a3],[a4],[a5],[a6]]
  }
   
}

Spreadsheet

https://docs.google.com/spreadsheets/d/1FH1nI6DAeMTnsynb4ntIpYSIKoClQWu5-AfoHUdHhak/edit?usp=sharing

[TIMAI2]

Put all the procedures that need to happen after “READ” has completed in the Web1.GotText block if func = READ section, then they will only be called once read has been completed.

If there is data generated in setCoordinates or displayCoordinates that is needed by WRITE, then make sure that they are also completed before calling WRITE. If setAngles and displayAngles are meant to run after WRITE has completed, then put those in the Web1.gotText func = WRITE section.

[Paozieu]

So I cant use this “when btnXminus.Click” block to call read, modify X and write it back to google sheet since the variable X is used in the web1.gotText ?

If this isn’t possible, I don’t have any ideas on how to do my app.

This is a screenshot of the design.

Do you guys know how I can move the sliders with the angle values I get from the google sheet when I press + or - buttons from the bottom of the screen ?

[TIMAI2]

Put the WRITE procedure in the "after READ" area

To set the sliders see here:

Incrementing a slider

see this explanation by @Boban from the old forum https://groups.google.com/d/msg/mitappinventortest/e-64Ldc1qbE/KK-en28sCAAJ min value -> max value 0 -> 100 each step has a value of 1 (100/100) = 1 0 -> 200 each step has a value of 2 (200/100) = 2 0 -> 255 each step has a value of 2.55 (255/100) = 2.55 0 -> 500 each step has a value of 5 (500/100) = 5 In your case when you have value 1-> 500 will be a little different as you already occupy 1 point/step, see the attachment… as you get th…

[clement_pomarede]

Hi,
Well I try to do your exemple but I don’t understand how script and spreadsheet can be linked ? Where does it appear in the script ?

[TIMAI2]

If you followed this:

How to make a link between an excel file and AppInventor

Here is my Simple Read / Write example, for Ai2 and a Google Sheet. SHEET We set the google sheet up with two sheets (tabs), and the google apps script web app is bound to the spreadsheet (created by selecting Script Editor from the Tools Menu) Sheet1 can be considered to be the workings sheet, with the calculations. Sheet2 is just there to receive the data from AI2 (which is then transferred to Sheet1 by formulas) The web app uses appendRow to pass the data into the google sheet on Sheet2.…

Where are you getting stuck ?

The google apps script is bound to the spreadsheet (not a standalone script)
This directly connects to the spreadsheet here:

var ss = SpreadsheetApp.getActive();
var sh = ss. getSheetByName("Sheet2");

[clement_pomarede]

Ok I think I understand the link.

But when I try to do the same thing as you, I have the same message as Paozieu (How to make a link between an excel file and AppInventor)

Here is my spreadsheet
My blocks :

And my script

Maybe, the error is from my spreadsheet which is in french ? I don’t know because I tested your project and it 's working…

[TIMAI2]

@clement_pomarede

But when I try to do the same thing as you, I have the same message as Paozieu

What error ? Like an alert notifier with a load of html ?

If so, this probably means you have not updated the script properly to a new version (you have to do this everytime you make a change), or it is not set to run as you (your google account) and accessible to "anyone, even anonymous"

[clement_pomarede]

Yes that’s what I have when I click on Write button :

But I republished the script as appweb and all files are all access.

[TIMAI2]

You have an authorisation problem somewhere…

Does your script url end with /exec or /dev ?

Have you given permissions to run the script ?

[clement_pomarede]

Well, I did all authorisations and it finally worked !
I’m sorry to have wasted your time…But thank you very much

[TIMAI2]