Strecklista

Digital strecklista som webapp för simpel hantering av streckvaror

New version

Since Google Sheets can only support roughly 10,000 rows a new version with a real database would be nice to have. Some work has been done by ECarlsson however it is far from complete. As I have graduated I have decided to stop updating this. Feel free to contact ECarlsson if you want to help!

App Features

• PIN-protected (no tedious personal logins)
• Save user as favourite to show at the top of the users list
• Live feed of transactions
• Swish into your account directly from the app
• Admin can send account balance emails in the app

Technical Features

• Device agnostic
• No extra hardware required
• Google Sheet as database for easy handling (for the admin)
• No cost at all!

Table of Contents

• Strecklista
  • App Features
  • Technical Features
• Table of Contents
• Demo
• Installation
  • Requirements
  • General information
  • Create database server (Google Sheet)
  • Set up database
    • General settings (tab: Inställningar)
    • Users list (tab: Användare)
    • Account balance email reminders (tab: Streckmail)
  • Deploy web app to Heroku
• Update to new versions
  • Update database Sheet
  • Update web app
• Usage
• Contributing

Demo

A live demo can be found at https://strecklista-demo.herokuapp.com and its corresponding database Sheet.

Installation

Requirements

• Google account (with Sheets language set as Swedish)
• Heroku account
• No previous coding experience!
• Some knowledge of Google Sheets / Excel

General information

The application is comprised of two parts:

• Database server (Google Sheets)
• Web app server (Heroku) Installation of the app will then be in two steps, first the database and then the web app.

Create database server (Google Sheet)

First you will make a database Sheet from a template and enable it for web requests.

1. Go to this Google Sheet.
2. Make a copy to your own Drive by clicking Arkiv -> Kopiera.... Give it a good name and place it in an empty folder where you can find it later.
3. Open the Sheet and then click the menu item Verktyg -> Skriptredigerare....
4. In the new tab click the menu item Publicera -> Implementera som webapp....
   • Grant any permissions it asks for. This is needed for the app to work!
   • In the window where it says the app is not safe click Avancerat and then click Öppna Strecklista (osäkert).
5. Select the following settings below and then click Implementera.

Set up database

There are some database settings which will be described here.

General settings (tab: Inställningar)

Here are the general settings for the web app.

• password - This is the password for the admin page in the web app.
• groups - A list of all the groups and in what order they should be shown. Note that users that are in hidden groups can still be selected in the favourite and plus menues.
• buttons - Which buttons should be shown in the action bar.
• mail_user - Email address and user name for Microsoft account which will be used to send out account balance reminders.
• mail_pw - Password for the account above.
• title - Title of the web app (shown as title of browser tab).
• pin - PIN code for accessing the web app. Set to 0000 to make it accessible by everyone.
• swish - Mobile number for Swish account
• mail_name - Name of admin. Will be shown in various parts of the app.
• negative - Global flag for allowing transactions if it results in a negative balance. There is also a flag for every user that overrides this option if it is set to Nej. This means that you can allow only certain people to have a negative balance.
• days_pin - How many days will pass until the user has to enter the PIN code again. Notice that if you change the PIN code all users will have to immediately enter the new PIN code.
• minutes - How far back the list of transactions will go in minutes. Apart from this limit the maximum number of transactions in this list is 10.

Users list (tab: Användare)

Here you you put all the users and their information. There are 10 possible fields:

1. CID - Unique identifier. If the user has no real CID, just write a non-existing one (and make sure to only send reminders to the user's registered email).
2. Namn - Full name.
3. Smeknamn - Nickname that will be shown in the app.
4. Grupp - Which group the user will be in.
5. Sortering - Which place the user should be in its group.
6. Får strecka - Has right to pay. If not, they will not show up in the app. ("Ja" or none)
7. Strecka med skuld - Has the rights to pay with debt. ("Ja" or none)
8. E-mail - The email address which will be used to send out reminders.
9. Mobile - Mobile number. Will not be used in the app but is good for the admin to know (for Swish).
10. Saldo - Account balance.

To add a user, add the desired number of rows and input the users' information. After you are done, you can click sort at the top of the columns.

Account balance email reminders (tab: Streckmail)

Here you can set up all the rules for account balance email reminders. You have the following fields:

1. Namn på regel - The name of the rule (short description for yourself).
2. Senast skickad - The last time this rule was sent out.
3. till följande adresser - Which addresses were used the last send out.
4. Skicka ut - Use this rule when sending.
5. Skicka till CID - Send to [email protected] instead of email.
6. Villkor - The condition (boolean) if the user is eligible for the rule. Javascript syntax.
   • This field can use the variables cid, saldo, rights, group, nick, name, email, tel.
7. Exkludera CID - If a certain user (CID) should never be selected by the rule.
8. Ämnesrad - Email subject line.
9. Meddelande - Email body text (can be HTML-formatted).

The last two lines can use the variables cid, saldo, rights, group, nick, name, email, tel, mail_name, swish but in the form %var%. For example if the subject line is Hello %nick%! then when the email is sent out %nick% will be replaced with that user's nickname.

The actual interface for sending these emails is located in the app (in the admin page). When logged in, you can first send a test email to an address of your choice to see that the app has access to the Outlook account. There is also a button for fetching a preview of all the emails that will be sent. Finally the last button is for actually sending the emails.

Deploy web app to Heroku

Now you will deploy the web app to Heroku.

1. In your Google Sheet and click the menu item Admin -> Skapa ny webapp (Heroku).
2. Finally press the button that says Deploy to Heroku.
3. After you have signed in at Heroku a dialog for deploying the app is shown. The name you choose will be the URL of your web app. For example if you choose min-strecklista the URL will be https://min-strecklista.herokuapp.com. Choose something descriptive, but still memorable and short. Now choose Europe as region and then click Deploy app.
4. Your web app should now be live and working!

Update to new versions

In order to update the app you need to first figure out if the version requires a Sheet update or not. You can do that by checking the latest version in the changelog.

Update database Sheet

You can skip directly to Update web app if the new version does not require a Sheet update. First you will update the backup file, then create a new Sheet and finally import the backup file into the new one.

1. Open your Google Sheet and click Admin -> Backup / Återsällning....
2. Click Ladda ner fil and then click the link to download the backup file. Do not lose this file!
3. Follow all the steps in Create database server (Google Sheet) to create a new Sheet from the template (but now it will be the latest version). Put it in the same folder as the previous one.
4. In the new Sheet file click Admin -> Backup / Återsällning... and then scroll down to Återställning.
5. Select your downloaded file from step 2 and then click Återställ.
6. Check the imported data to make sure that everything has been uploaded. If everything looks OK, you can safely remove the old Sheet file. If not, complete step 2 (create backup file) and try from step 4 again (upload file).
7. Follow the steps below in Update web app to create new version of the web app.

Update web app

1. Go to Heroku and navigate to your app.
2. Go to Settings, scroll down and click Delete app... and follow the instructions to delete the app. Don't worry, all your data is safe.
3. Follow the steps in Deploy web app to Heroku and set the name as the previous name (to keep the URL).
4. You may need to clear your cache before the web app will show up for you.

Usage

User instructions can be found in the actual web app. Check out the demo!

Contributing

If you have an idea for a new feature or found a bug please create a new issue.

A valid issue should

• Have an appropriate label
• Describe the idea/issue
• Back up the idea/issue with good enough arguments
• State your intentions, e.g. are you going to code it yourself and make a pull request or want someone else to do it

If the issue is well recieved, you can create a fork and start coding. A bad issue will lower the chances of approval for your pull request!