Our support site has moved to https://support.intuiface.com

Set a value for all cells in a column of an Excel file

    Description & Use Case

    When using Excel as a data source for an experience, you may find yourself needing to reset all values in a column to their default value. For example, perhaps you want to prep your IntuiFace experience for a new user. Perhaps it's a quiz where each user will have different answers or it's a checklist that should be reviewed by multiple people each day of a week.

    This article will describe how to reset all values in an Excel column, with any number of rows, using just 3 actions:

    • Set Value of a Simple Counter
    • Substract one of a Simple Counter
    • Update cell of an Excel Interface Asset

    Explanations

    The key step is to create a loop (or an iteration) that will walk down the column, one cell at a time.

    A developer would write a piece of code like this:

    var i = table.length;  // initialize a variable to the length of the table
    while (i > 0){  // go through all items of the table
       table[i] = defaultValue  // set the default value to the current line
       i = i - 1 // go to next line
    }
    

    You can reproduce this mechanism in IntuiFace using the Simple Counter interface asset. Here are the steps:

    • Add a Simple Counter to your experience.
      • Note: You do not need to visually represent your counter in the scene. It will act as a "logical component" in the background thanks to its triggers and actions.
    • Use a button or trigger of your choice to begin the value resetting process.
      • Whatever trigger you use, call the Set value action of the Simple Counter
      • Bind the counter's Value parameter to the Number of rows property of your Excel spreadsheet
        Counter-SetValue.PNG
    • On the Simple Counter interface asset, add a trigger Count greater than and use 0 as its parameter
      CounterTrigger.PNG
    • On this Count greater than trigger, add the 2 following actions:
      • Update cell on your Excel sheet, with:
        • Row Number bound to the Count property of your counter
        • Column: the column for which you want to reset the values
        • Value: the value you want in every cell of the entire column.
      • Substract one on the counter, to iterate to the next row of your Excel file.
        • We suggest adding a start time (ex: 0.5 sec) to see all the steps of your reset while testing. You can reduce it afterwards.
          Counter-Action1.PNG
          Counter-Action2.PNG

    Sample

    You can download a light sample by clicking this link

    Excel-ResetCounter.gif