How to make a link between an excel file and AppInventor

[ABG]

iferror() can be done using the ifthenelse value block and the
is-number block, and appropriate tests.

There is also a Screen1 error catcher block, but that’s too blunt an instrument.

Here’s a starter app …
kinematics.aia (453.7 KB)

[ABG]

If the blocks are too slow at run time, you might need to switch to
similarly named JavaScript functions in a web page in the Media folder.
Search for ‘FAq Javascript Stunts’ in this forum.
I’m not an expert on that, though.

[Paozieu]

It seems easier than I thought, It’s just time consuming.

I will make the spreadsheet into blocks and see if it’s too slow.

[Paozieu]

I’m having trouble making the iferror function.

How do say that the cell O25 is either DEGRES(ATAN(ABS(E16)/F15) or else 90 ?

[ABG]

You used the wrong if/then block.

Use the one with the nipple on the left.

Since the division and the atan() function are the ones that cause an error if inputs are out of bounds,
wrap them with bounds tests on their inputs, like
if F15 = 0 then don’t do the atan but return 90
else do the atan()

Be aware there might be an atan2() block that might not need the division.
Also, I would double check if you need that block that converts radians to degrees.
Does AI2 atan() return degrees already?
Each block has a tool tip visible if you hover over it.

For team coordination, a free github project would make a handy place to share those procedures, one per .png file.

kinematics.aia (454.2 KB)

[ABG]

After sleeping on this, I suspect you need to add a design layer to
your code with value procedures expressing Excel versions of all the AI2 trig blocks, for use with radians instead of degrees.

(Excel trig tutorial: http://science.clemson.edu/physics/labs/tutorials/excel/trig.html)

This would allow direct transliteration of the Excel formulas into AI2
formulas calling the new layer of value procedures.

Here’s a package for you …

Excel_trigonometry.aia (2.3 KB)

This is a trigonometry library for people trying to convert an Excel trigonometry formula to an AI2 trigonometry formula. Everywhere the Excel formula calls a trig function, replace that call in AI2 with a call to the equivalent function in this library. Abe Getzler

[Paozieu]

Thank you for your input, I will first check if the blocks work with the regular cos and sin functions and use this library in case it doesn’t work.

[Paozieu]

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

here’s the app if you want to test it : Arduino_Robot_Arm_Control_Forward_Kinematics.aia (261.3 KB)

It takes about 25 seconds for my phone to calculate new coordinates, and this is just the forward kinematics.

Everything works fine until the app calculates the multiplications.

I will just go back to making an app script to use my google spreadsheet.

If you have any ressources I can work with, I will gladly study them !

[SteveJG]

Sorry AI2’s Math blocks provide a slow calculation. Math block calculation is very SLOW compared to what is possible using javascript or using a compiler like the professional Android Studio.

AI can use javascript to perform calculations. You might use it to do your calculations … here are links to some possible javascript code. Depending on exactly what you need to calculate javascript Finding Angles might help if using a spreadsheet does not pan out. .

[Taifun]

@Paozieu

If you have any ressources I can work with, I will gladly study them !

see an App Inventor/JavaScript example here App Inventor Tutorials and Examples: Fast Calculations | Pura Vida Apps

Taifun

---

Trying to push the limits! Snippets, Tutorials and Extensions from Pura Vida Apps by Taifun.

[Paozieu]

Hello TimAI2,

Can you give me more detail on how to use your script to read and write to a google sheet ?

I can’t find anywhere how to call a google apps script web app bound to the spreadsheet from app inventor.

Here is your Google Apps Script Webb App code plus some other code I found here :

function doGet(e) {
  
  return ReadOrWrite(e);
}

function ReadOrWrite(e){
  
  // Read from A1 to A6
  if(e.parameter.func == "READ"){
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheetByName("Sheet1");
    var rg = sh.getDataRange().getValues();
    var outString = '';
      for(var row=0; row<7 ; ++row){
        outString += rg[row].join(',') + '/n';
      }
    return ContenrService.createTextOutput(outString);
  }
  
  // Write from A1 to A6 and read from B1 to B6
  else if(e.parameter.func == "WRITE"){
    var ss = SpreadsheetApp.getActive();
    var sh = ss. getSheetByName("Sheet2");
    sh.appendRow([e.parameter.a1,e.parameter.a2,e.parameter.a3,e.parameter.a4,e.parameter.a5,e.parameter.a6]);
        var sht = ss. getSheetByName("Sheet1");
        var rng = sht.getRange('B1:B6');
        var vals = rng.getValues();
        return ContentService.createTextOutput(vals); 
    }
}

Is this code correct ? I don’t really know how to test it.

In app inventor, I would like to :
-> call the READ function of the script
-> add 1
-> call the WRITE function of the script

You also said that I need to build a url to the sheet. I have to use some join text blocks but I cant figure out what part of the link to the script I should write.

My questions are :

What is the url of the script ? (I wrote “something something” istead in the blocks
Am I going the right way ?

here is the link of the script : https://script.google.com/d/1PliiM3sCXtQTrXzy7SbSr9it1EcWPmaMSdTj3VPprkbKvPyrgqbA3UYr/edit?mid=ACjPJvGVXqMGc_EB6MAAC8xwjrEko4GV4Mi-X1-hOdHqf92wEO41kV7w7leJ1GRj8zJjaEB9I0HYOTwWCq5CvS6Rg6IQMqrTLMC2gIxvP3E8gw7--2pvBcQsT6VRuz7MteR3VvijeTyJBlCM&uiv=2

Here is my advancement in app inventor :

To finish, I am sorry if there are ressources online which are already answering my questions. I can’t figure out which words to type in google.

Thanks !

[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…