App inventor Google sheets

[christop...@dalkiafroidsolutions.com]

Bonjour,

Je revient vers vous sur le meme sujet : https://groups.google.com/d/msgid/mitappinventortest/fb1a9325-5472-4644-b41f-17eb72ceb718%40googlegroups.com
sauf que maintenant je souhaiterai faire une recherche via Google sheets avec des chiffre et des lettre ex :"MAT-00272".
Es que cela est possible ? Si oui pourriez vous m'aider à le realiser ?

Merci de l'aide précieuse que vous pouvais m'apporter.

Christophe.

[TimAI2]

Some example data would help...

What is in your google sheet ?

[Chrisss59]

Bonjour,

dans textbox1 je rentre l'ID et dans label1 qu'il m'affiche le reste de la ligne qui correspond a l'ID.

merci

[TimAI2]

Would it be better to have the ID's all in a list, so the user can simply select one, instead of typing ?

Otherwise you will also need to do some error checking to ensure the correct format is entered...

Do you only want to search for the "MAT-xxxxx" entries or any entry?

If so, how many different types will there be: MAT-xxxxx, TAV-xxxxx, .....?

How many records will there eventually be in the table?

Sorry to ask all these questions, just want to provide the best solution for you....

[Chrisss59]

Bonsoir,
Pas de soucis pour toute ces question ces déjà gentil de votre par de m'aider à réaliser ce projet

En faite je ne vais pas le saisir mais le scanner en QR Code.

Pour l'ID il va en avoir beaucoup je serais pas vous dire le nombres pour le moment.

Ensuite pour la table il y aura a peut près 10000 enregistrement.

Merci pour votre aide.

[TimAI2]

OK - there are two routes:

1. If your spreadsheet can be public / anyone with the link

2. If your spreadsheet must remain private

Given the number of records its makes more sense to query the data

as opposed to downloading the entire dataset to the app each time

If 1 then we can create a simply query in Ai2

If 2 then we must use an intermediate google web app to query the private sheet

Private or anyone with the link spreadsheet?

....

[TimAI2]

Here is the first one with the spreadsheet shared with "anyone with the link"

BLOCKS

SCREEN

The "Scan Code" button is replicating a scanned code, using a list of the codes in the spreadsheet

You would replace this with blocks and components for your scanner, and feed the result to the Web1.Get

[Chrisss59]

Bonjour est merci pour ce code.
Par contre c'est pas grave si l'ID je ne les pas a l'avance sur la table car il rentre au fur à mesure via un formulaire odk!!
Aussi pour créer un code avec feuille privé il faut créer une page connexion Google via mail?
Si c'est le cas je vais devoir partagé la feuille de données au utilisateurs ??
Je ne voudrais pas la partagé au utilisateurs.

Merci beaucoup pour votre aide

[TimAI2]

"Anyone with the link"

Means what it says, if you have the link to the spreadsheet you can view it and open it, make a copy, you can't edit it.

Chances of anyone finding the spreadsheet if they do not have the link are something like 8 billion to 1

You can use the Obfuscate text block to hide the spreadsheet ID in the compiled app.

You do not have to explicitly share the sheet with users for my example to work, just use the ID in the app. Users will not normally see it.

"Private Google Sheet"

Accessing the data when the spreadsheet is private requires use of a google web app using google apps script.

This web app runs "as you" but can be actioned by anyone, therefore requires no authentication by the user

which means the data can be accessed and shared by the web app. The web app can be bound to the spreadsheet.

My current method is to call in all the data to the app, then query it. This might be a bit slower....

Hope this helps :)

[Chrisss59]

Merci cela m'aide beaucoup.
Je vais rester sur le partage de lien.
Par contre au niveau du vCode je suis obligé de renseigné toute mes ID, car dans ma table je ne l'ai pas toute car elle vont ce remplir automatiquement via un formulaire Odk collect.

[TimAI2]

Your scanned code will replace the output of the vCodes

[Chrisss59]

D'accord merci.
Une autre petite question:
Au début de mon apk j'ai créer un Mot de passe à 4 chiffre qui vient récupéré le nom du client via une table google, ensuite maintenant je voudrais que chaque client est seulement accès à leurs lignes quand il vont scanner l'ID
Ex: si client A est qu'il scanne un ID d'un client B ça ne fonctionne pas.

Pensez vous que c'est possible ??

Car je voudrais travaillez sur une seul table est pas sur plusieurs avec des tri sur chaque table.

Merci.


https://docs.google.com/spreadsheets/d/1e6mCYnFhypTmmHWgY9PiYYx9HSsv_0J4W1ZU_ll3ACw/edit?usp=drivesdk

[TimAI2]

Should be simply a case of adding the client password to the query:

SELECT * where A contains 'textbox1.text' and B contains 'textbox2.text'

Where Column B (but it could be whichever column you choose!) in the spreadsheet table contains the client passwords and textbox2.text the client's password (or wherever you choose to store it, e.g. in a variable)

Try it ...

[Chrisss59]

Bonjour
Je vous remercie ça fonctionne,

Pourriez vous me dire comment intégrer le code ci dessous a votre code.
Je voudrais pouvoir télécharger un fichier PDF en triant ma colonne B

Ex télécharger un PDF juste avec le client "A"

LienPDF_inventaire :

https://docs.google.com/spreadsheets/d/1e6mCYnFhypTmmHWgY9PiYYx9HSsv_0J4W1ZU_ll3ACw/export?format=pdf&size=0&fzr=true&portrait=false&gid=0

Merci

[TimAI2]

If you must have a pdf, as far as I know there are two options:

1. Create a google web app with some google apps scripting that generates the data required on the sheet in a new tab and then returns the download
2. Download the data to the app, then use Chrome and a javascript library to generate a pdf table (there is a free extension available for this, but your clients would need to have Chrome installed on their devices)

If you only want to display the data on the app, then we download the data and generate a table in a webviewer or create a display for the table data

[Chrisss59]

Le code que j'ai mis en pièces jointe fonctionne mes il télécharge la table complète, mais le problème c'est que j'aimerais le trié en B par client !

Je voudrais faire le même principe, croyais vous que c'est possible ?
Si oui pourriez vous m'aider ?

Dans le code le "LienPDF_inventaire" correspond a ça :

https://docs.google.com/spreadsheets/d/1e6mCYnFhypTmmHWgY9PiYYx9HSsv_0J4W1ZU_ll3ACw/export?format=pdf&size=0&fzr=true&portrait=false&gid=0

Merci

[TimAI2]

I must refer you to my previous post !

Either do the work in the google sheet or on the app...

[Chrisss59]

D'accord,
Je préfèrerais sur l'application.

[TimAI2]

Well, I have had a look at the on app solution (both with the extension and my own handcrafted effort), and I am not happy with the functionality, 

the chances of your user getting stuck in Chrome and losing control of the app are too high!

I will therefore have a go at the web app solution given we are already using google sheets.

I will not be available for the next 24 hours, so it might be late on Wednesday 27th or after that I get a working example done.

[TimAI2]

I have a working solution, just need to grab some images for you on how it all works.

[TimAI2]

I quite like this :)

The requirement: filter a spreadsheet dataset by values in a column, then download that output to a pdf on the device

3 stages:

1. Set up spreadsheet
2. Write web app
3. Blocks and components for app

1. Set up spreadsheet

We need to add two more sheet tabs to the dataset, the first to hold the filtered results and to display them, the second to contain the query value (e.g. Client ID)

The images below show the sample dataset, the query value, and the output:

 

This way you have full and easy control over the layout and format of the output sheet. I have frozen row 1, this ensures that this row is displayed on all pages.

You will see the query equation (copy this to your spreadsheet) pulls in the value from Sheet3 to carry out the query. It is this value we will update using the webapp

2. Web App

Use a bound script (so go to Tools > Script Editor to create a new project)

A variation on code used a lot for adding data to spreadsheets from AI2

Just needed to clear the contents of the sheet each time to ensure the value is replaced and not added

and to provide a responseContent of the query value back to the AI2 app

//for testing with PC browser

function doGet(e) { 

  var ss = SpreadsheetApp.getActive();

  var sheet = ss.getSheets()[2]; 

  var id = e.parameter.id ; 

  sheet.clearContents();

  sheet.appendRow([id]);

  var output = sheet.getDataRange().getDisplayValues();

  return ContentService.createTextOutput(output[0][0]);

}

//for use with AI2

function doPost(e) { 

  var ss = SpreadsheetApp.getActive();

  var sheet = ss.getSheets()[2]; 

  var id = e.parameter.id ; 

  sheet.clearContents();

  sheet.appendRow([id]);

  var output = sheet.getDataRange().getDisplayValues();

  return ContentService.createTextOutput(output[0][0]);

}

The script needs to be saved as a web app, run by the owner of the spreadsheet, but accessible to "anyone, even anonymous". You will need to give permissions to run the script

Once published get the url to the script for your AI2 app

The reason for the doGet AND the doPost is because if you submit via a PC browser this preforms a "get", whilst in AI2 this performs a "post"

You can test in PC browser to make sure it works with a url like this:

https://script.google.com/macros/s/AKfycbz1haDdC1lmXhpXYrJ58egMc8KBgJEcefTxT2jW-qNpW51emB4/exec?id=B

This should return a white html page with a "B" on it, and if you look at the spreadsheet, Sheet3, you will see it has a "B" on it

Right, that is all the non AI2 work done!

3. AI2 App Components and Blocks

For my example, I used a spinner that contained the client IDs i.e. A,B,C

Your app will apply this ID based on their login to the app

For testing i simply select a client ID, then press the button to Download the PDF

We need to add two more web components, one for the call to the web app, the other to call the pdf

BLOCKS

When the webapp has worked, it will report the value of the cell in Sheet3 e.g. A,B,C

and compare this with the value selected in the spinner.

If these are the same then it will initiate Web3 to download a pdf of the filtered results

if not, then to display a message

Files in this example as saving to the root folder of the sdcard

note: if the webResponseFileName is the same as a file that already exists, it will overwrite that file.

SCREENS

That is it ! Enjoy

[Chrisss59]

Bonsoir TmiAI2

Je te remercie beaucoup pour ton aide est pour c'est code, c'est excellent.

J'ai une autre petite question, j'ai essayé le tuto https://www.hackster.io/taifun/trigger-ifttt-to-send-an-email-using-app-inventor-9df505.
Mais je n'y arrive pas, problème provenant de Gmail voir capture écran.
Serais-tu me dire d'où vient le problème ?

Merci

[TimAI2]

I do not have any experience with the IFTTT email solution

Perhaps Taifun can advise if any issues have arisen with this method ?

Please start a new thread for this.