How to make a link between an excel file and AppInventor

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.

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 !

Yes, that is my scripting as well :wink:

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

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

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.

sheet2simplereadwrite

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.

sheet1simplereadwrite

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

@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.

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:

image

  1. 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

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 :

blocks (1)

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

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.

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 ?
blocks
blocks (1)
blocks (2)

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 ?

Put the WRITE procedure in the "after READ" area

image

To set the sliders see here:

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 ?

If you followed this:

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");

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…

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"

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.

You have an authorisation problem somewhere…

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

Have you given permissions to run the script ?

1 Like

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

thumbsup2

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.