I would like to make an app that can write data to 6 cells in an excel file, then the excel file would do some calculus and the app would retrieve data back from 6 other cells from the same excel file.
Is there any way I can do that ?
I tried with csv files but it didn’t make all the calculus of the excel file.
I was thinking of hosting a website with the excel file inside (I don’t know how to do that) and with the web connectivity of app inventor, send and retrieve http requests.
I just tried google sheets and it handles the calculations. I am new to google sheets too, How does the connectivity work ?
It’s actually matrix calculations, And I need some functions like iferror to make it work. So doing the calculations on appinventor isn’t possible I think.
For the calculations, if you give an example of what has to be done, our resident maths gurus may offer a solution. It makes more sense to do it on the app, so worth exploring this first.
For data exchange with google sheets, it is a matter of setting the sheet to receive, process and return, and using the web component to handle this. You might need an intermediate script project.
Nothing there in the sheet I can see that can’t be done with the built in maths functions in AppInventor. Obviously easier to see/do on a spreadsheet for a newbie
Perhaps the easiest way to send the values is to append a row to another sheet - with the values linked to O4:O9 and to then get the values in Q4:Q9 for the return…something like:
Google Apps Script Web App (bound to the spreadsheet)
doGet(e) {
var ss = SpreadsheetApp.getActive();
var sh = ss. getSheetByName("Sheet2");
sh.appendRow([e.parameter.o4,e.parameter.o5,e.parameter.o6,e.parameter.o7,e.parameter.o8,e.parameter.o9]);
var sht = ss. getSheetByName("Sheet1");
var rng = sht.getRange('Q4:Q9');
var vals = rng.getValues();
return ContentService.createTextOutput(vals);
}
(not tested)
In the AppInventor app, you build a url to the sheet, including all the parameters o4 to o9
Thank you very much for your answer ! There is something I forgot to mention.
The coordinates that i send are only relative to the ones that are in the sheets.
To be clear, the app sends numbers like
+10mm to axis X
-2mm to axis Y
+6° to Yaw
…
I don’t really know how to do this, but the app needs to :
-> get the current coordinates from the spreadsheet (O4:O9)
-> add or substract the numbers to it
-> send the new coordinates back to the spread sheet (O4:O9)
-> get the joint angles from the spreadsheet.
Clientside, the script should do :
-> send the current coordinates from the spreadsheet to the app (O4:O9)
-> WAIT for the new coordinates to come back from the app
-> get the new coordinates
-> return the new joint angles. (Q4:Q9)
I know the script you wrote does the last two steps, but I don’t know how to get the script to wait for the app to send coordinates first.
If you want to do it entirely in blocks,
you can create a collection of value procedures, one per Excel cell,
each named after the cell (i.e. O23) containing that particular formula.
In the procedure definition, duplicate the calculation from that Excel cell,
using value procedure calls for the cell references.
This will be tedious and dumb, but can be split up among a team
and can be combined via AI2’s Download as Block Image option,
one image per function, and the Block Editor’s secret
Drag and Drop feature.
By the Way, double check the AI2 docs for its trig functions.
Are they in degrees, or in radians?
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.
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.
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.
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
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. .
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 ?
Taifun.