Google-sheets script look up data and display in a popup message with advanced google services

0
votes

I am creating a spreadsheet to document rental data.

I have client information on one sheet (Clients), and rental info on an other sheet:

Rental.

[ID]    [Name]  [Phone] [Product Code]  [Product Name]  [Price]

Clients:

[Name]      [e-mail]   [Phone]

The unique identifier for the clients are their phone number.

To be user friendly, on the Rental sheet the name of the client is used to by receptionist to log the rented equipment, and the unique ID (phone number) is used as a hidden data in the record.

When there are multiple clients with identical names, I use a popup window with HtmlService.createTemplateFromFile and SpreadsheetApp.getUi().showModalDialog where the receptionist can click on the phone number of the actual client in question.

I am stuck at the part, where I look up the list of phone numbers from the sheet Clients based on the data in getRange(SpreadsheetApp.getActiveSpreadsheet.getActiveRange.getRow(),2 (the 2nd column of the active row on the Rental sheet.

I can't use the built-in functions of sheets like Filter or query form the API script.

I really want to avoid migrating the whole project to google sql database if possible.

Any suggestions or pointers are appreciated.

1
google-apps-scriptgoogle-sheets
can you show the code you're already using? – e__n
Uniquely identifying by phone?? You know people can change that, right? – tehhowch
Yes, I know. This is a POC, eventually I want proper user management, where they can access and update their personal information and I'll use a generated unique ID, and an SQL database. For now though, for the scale I'm using for, it's the most work-efficient way. Thanks for the warning though! :) – Peter Varga

1 Answers

0
votes

1) Define the lookup field for the Clients sheet ('Name')
2) Use the onEdit(e) trigger to handle edit events in the Rental sheet and check if the 2nd column (Name) was edited. More info on simple and installable triggers

https://developers.google.com/apps-script/guides/triggers/

https://developers.google.com/apps-script/guides/triggers/installable

3) Get the values from the 1st column of the Clients sheet ('Name'): 

 var rows = sheet.getRange(2, 1, sheet.getLastRow - 1, 1).getValues()

and filter them based on user input. 

    rows = rows.filter(function(row){ return row[0] == userInput });

If more than 1 value is returned, display the popup, passing the variable to the HTML template. More on how to do that 

if (values.length > 1) { //do something }

If you are not confident enough using GAS, please refer to the official documentation.