Build an App! With no Coding Experience. Google Sheets + Forms.

I need an App….

One that updates in real time as responses are collected and most importantly, one that is free.

Here’s the scenario: I have a bunch of retail workers and some shifts inevitably don’t get claimed on my scheduling tool. I need a way to dynamically incentivize any open shifts, as they get claimed I need to take them off the board.

The workers are paid commission on their sales, which creates a dynamic where some times to work are more lucrative than others, leaving gaps in the store coverage. Peggy has claimed the times below with the ✔️.

Scheduling with King of the Hill

What makes matters more complicated is I have many stores - some of the employees can work in many stores, while others can only work in one.

How do I influence Peggy and Hank to take some of the low $$ times? I need at least 1 person to be in the stores at all times.

The Strategy

At what $$ amount does it become worth it to take the shift? The idea here is to price the incentivized shift so it is:

• Attractive to the worker
• Feels like someone else will take it if they don’t (FOMO)
• Prioritize the store with the lowest # of options available first
• Economical for the business

I need to be able to test a plethora of strategies and figure out which has the greatest influence to the workers.

Questions that this App will help me answer:

1. What dollar amount changes the scheduling behavior of my retail worker?
2. Should the value of the shift increase with time or decrease with time? One would creates a sense of urgency.
3. Do the workers value different dollar amounts at different times? Is their time worth more on a weekend vs. a weekday?

The Requirements

There’s a lot of solutions we could pursue, it could be an application in python or use a no code app like App Sheet, Retool, or PowerApps.

My tools: Looker, Google Sheets, Google Forms, App Script

List of requirements:

1. Free for users and me
2. Near real time data feed — needs to update every 5 minutes if possible
3. Each worker has unique and dynamic pricing
4. Each worker has their unique view
5. Only one worker per store & per time

The Solution

Let’s just jump right into how we can solve this with Google App Script, G-sheets/forms.

Architecture:

The flow is to determine any gaps and merge that with any retail worker that is eligible for that gap. A base price will also be set per store some stores-hours may need to be manually controlled.

After those are ported into Google Sheets using an import range or copy/paste, we will structure the data to then be received into Google Forms. App Script will act as the service that will transmit that data to the form and ensure that it is up to date. This is what allows the data to reflect the sheets most recent update, giving it a near real time data feed.

The end result will look something like below. The worker will first select their name, then they will be taken to a screen that shows the various stores and corresponding incentive amounts.

Worker chooses incentivized shift

Once a shift is selected, the response is recorded in the responses section. In our case, we will be linking it back to the original sheet so two workers can’t sign up for the same time.

Setting up your sheets

This is going to get formula heavy — I will link the sheets so it helps get you started.

If Zach G can do it. You can do it.

Sheet Architecture:

You will need 6 tabs:

Tabs:
  Retail Worker Details - contains each employee stores and clients can they work for.
  Incentive Value - determines what each client/store is worth.
  Raw Data - contains the gaps needed, covered, and eligible workers.
  Coverage Gap Layer - summarizes/merges the info from Incentive Value & Raw Data.
  Shift Summary - summarizes/merges the gaps that can be covered at the worker level.
  Form Ingestion - puts the data in the format the Google Form can intake.
  Form Responses - you connect each form to a new tab in the Google sheet.
  Responses Structured - aggregate all the form responses, feed back to coverage gap layer.

Sheet Architecture

Steps/formula in Coverage Gap Layer Tab:
  1. Pull data from Raw data tab:
      A2: =UNIQUE('1.Raw Data'!C4:F,False,False)
  
  2. Look up incentive value on Incentive Value Tab:
      D2 = IFNA('2.Incentive Value'!$D$1*VLOOKUP(CONCAT(C2,B2),'2.Incentive Value'!$A:$E,5,false),0)
  
  3. Look up to see if anybody signed up in Responses Tab
      G2 = IFNA( TEXTJOIN(",",True, FILTER('Responses-Structured'!D:D, REGEXMATCH('Responses-Structured'!A:A,B2),REGEXMATCH('Responses-Structured'!A:A,E2),REGEXMATCH('Responses-Structured'!E:E,C2))),"")
 
  4. Count to see if anyone signed up in G2
      H2 = if(LEN(G2)>0,1,0)

Make sure to drag formulas down (or you can build an array formula around this)

Steps/formula in Shift Summary Tab:
  1. Copy from Raw data tab:
      A2 = UNIQUE('1.Raw Data'!F4:I)
  
  2. Format the Day (this will make up our string at the end)
      E2 = TEXT(B2,"ddd-dd")
  
  3. Look up the retail worker's clients & locations
      F2 = VLOOKUP(A2,'3. RetailWorkerDetails'!A:B,2,false)
      G2 = VLOOKUP(A2,'3. RetailWorkerDetails'!A:D,3,false)
  
  4. Find any gap that matches the date-time, workers clients & locations
      H2 = TEXTJOIN(",",true,FILTER('4. Coverage Gap Layer'!B:B,SEARCH('4. Coverage Gap Layer'!B:B,F2),SEARCH('4. Coverage Gap Layer'!C:C,G2),B2 = '4. Coverage Gap Layer'!A:A,'4. Coverage Gap Layer'!H:H =0))
      I2 = TEXTJOIN(",",true,UNIQUE( FILTER('4. Coverage Gap Layer'!C:C,SEARCH('4. Coverage Gap Layer'!B:B,F2),SEARCH('4. Coverage Gap Layer'!C:C,G2),B2 = '4. Coverage Gap Layer'!A:A,'4. Coverage Gap Layer'!H:H =0)))
      J2 = IFNA( Sum(FILTER('4. Coverage Gap Layer'!F:F,SEARCH('4. Coverage Gap Layer'!B:B,F2),SEARCH('4. Coverage Gap Layer'!C:C,G2),B2 = '4. Coverage Gap Layer'!A:A,'4. Coverage Gap Layer'!H:H =0)),0)
     TLDR: We have to search a string (Domain,Congress) to see if it is in of the Coverage Gaps
  
  5. Prep the string for Form Ingestion
     K2 = CONCATENATE("$",J2,"/Shift", TEXT(B2," ddd-dd")," ",text(C2,"hAM/PM-"),text(D2,"hAM/PM "),"PST | Stores In Demand:",H2)
     End result: $25/Shift Thu-16 7PM-12AM PST | Stores In Demand:Domain

Steps/Formula in Sheet1
  1. Write the name of the retail workers: A1:Z
  2. Look up all their eligbile incentivized shifts
      B2 = IF(IFNA(FILTER('5. Shift Summary Tab'!$K:$K, B1 = '5. Shift Summary Tab'!$A:$A,'5. Shift Summary Tab'!$K:$K>0),"No Incentives Available At This Time") ="","No Incentives Available At This Time",IFNA(UNIQUE(FILTER('5. Shift Summary Tab'!$K:$K, B1 = '5. Shift Summary Tab'!$A:$A,'5. Shift Summary Tab'!$K:$K>0)),"No Incentives Available At This Time") )

Steps/formula in Reponses Structured
  1. Gather all the responses into a summary sheet - you can add more forms into the query formula
      B3 =query({'Form Responses 2'!$A$2:$G},"Select * where Col1 > date '"&TEXT(A2,"yyyy-mm-dd")&"'",-1)
  2. Add the client details in column I
      I3 = =VLOOKUP(D3,'3. RetailWorkerDetails'!A:C,3,false)
  3. Text join the data together
      A3 = =TEXTJOIN(",",true,E3:AA3)
    
This ensures that the Coverage Gap Layer formulas add in anyone who signed up.

Setting up your forms

If you have made it this far, you are in the home stretch. Congratulations.

Steps:

1. Go create a new form. (you may need to create many — if you have over 10 people)
2. Write the names exactly as they appear in your sheet in Section 1
3. Section 2: First workers name — Madi P
4. Select checkboxes as the format
5. Make sure to point the responses BACK to your Google Sheet
6. Write some App Script Code

Worker selects their name

Worker chooses incentivized shift

Link back to the Gap App Spreadsheet

App Script Code:

I picked this up from a guy on YouTube, here’s the link to his video. It’s pretty detailed and there are several like it if you want to know more about the code. Only two steps you need to do is update your SSID and Form ID, you can grab them from the URL.

In your form, go to Script Editor. Then, you should see a file ready to go. Just delete everything in it and paste the code below. Make sure to save.

var ssID = "INSERT SSID HERE"
var formID = "INSERT FORM ID HERE"

var wsData =SpreadsheetApp.openById(ssID).getSheetByName("Sheet1");
var form = FormApp.openById(formID);


function main() {
 var labels = wsData.getRange(1,1,1,wsData.getLastColumn()).getValues()[0];
 labels.forEach(function(label,i) {
   var options = wsData
                  .getRange(2,i + 1,wsData.getLastRow()-1,1)
                  .getValues()
                  .map(function(o){return o[0]})
                  .filter(function(o){return o !== ""});
  
  updateDropDownUsingTitle(label,options)
 });

}


function updateDropDownUsingTitle(title,values) {
  var items = form.getItems();
  var titles = items.map(function(item) {
    if(item.getType() == 'CHECKBOX'){
     return item.getTitle();
   }
  });

  var pos = titles.indexOf(title);
  if(pos !== -1){
    var item = items[pos];
    var itemID = item.getId();
    updateDropdown(itemID,values)
  }
}

function updateDropdown(id,values) {
  var item = form.getItemById(id)
  item.asCheckboxItem().setChoiceValues(values)
  //var items = form.getItems();
  //Logger.log(items[0].getId().toString());
}


// Determining type of items item = items[3]
  //  itemtype = item.getType(); 
    // return Logger.log(itemtype);

Setting Up Your Triggers:

We used this for our healthcare marketplace for some time and we ended up getting rate limited by Google 😆. This is what determines how up to date your form stays.

1. Go to Triggers > Add Trigger
2. Select the timeframe you want it to refresh at (make sure main is selected as the function)

Time Frame

That’s all Folks:

Look. You could definitely build this all in App Script OR you could spend time and money for one of those no code apps OR you can develop it yourself.

However, for those strapped for time and money, this is an excellent stop gap. It won’t solve all your problems but, it can free you up to learn a little bit more before you build the real thing.

Limitations: I have 100+ retail workers I need to incentivize. Unfortunately the refresh time really slows after you add more than 10 people to the form (takes 15–20 minutes for anything over 10). If you want to keep it functional, limit <10 people per form (refreshes under 1 minute). Just add more forms (with the script too), as you need. You will have to set up triggers for those as well.

If you have any questions feel free to comment, I will be happy to answer any of them!

Link to Form

Link to Sheet