Designing Spreadsheets

You can import an existing spreadsheet from another Instance, or you can create a new one.

Begin at the Instance homepage, and select Data… Spreadsheets.

 

Creating a new Spreadsheet

First, you create the empty spreadsheet and define how it is to be used.

• Spreadsheet Name… Enter a name for the spreadsheet.
• Who can access this spreadsheet?… Define who will be able to view and edit the spreadsheet by selecting ‘Anyone’ (includes everyone except Basic User Roles), or one or more Roles.
• Who will be displayed when editing the spreadsheet?… Define whose data will be included in the spreadsheet by selecting ‘Everyone’ or one or more Roles.
• Team Filter – You can further filter the people included in the spreadsheet data by select a Group from the Team Filter list.
• Notes … You can add notes for internal use if necessary.
• Include members with an inactive status … By default, inactive people are not included in the spreadsheet.
• Only show people whose value from (optional) … Filter the included people based on a score value.
  • Select Score … select one from the list.
  • Operator … set to ‘Equals’, ‘Greater than’, ‘Less than’, ‘Not equal to’, ‘Greater than or equal to’, or ‘Less than or equal to’
  • Enter value … to set the condition.
• Allow spreadsheet to be … Check the appropriate options.
  • Edited … allows data in this spreadsheet to be input and edited (i.e. not just viewed).
  • Imported … allows data to be imported into this spreadsheet from a CSV (perhaps from a previous Instance).
  • Exported … allows data to be exported from this spreadsheet to a CSV file.
  • Synced with Mobile (BETA) … enables data to be entered/edited via mobile device (under Surveys in the GideonSoft app).
• Click “Create Spreadsheet” to save the settings and begin defining the content of the spreadsheet.

 

You can always return to the Spreadsheet settings by clicking the gear icon. The spreadsheet at this point is not active (i.e. has not been published). One or more content items must be added in order to publish.

Defining the structure (Columns) of a Spreadsheet

The next step is to build out the structure of the spreadsheet, by setting up columns/fields. Spreadsheets always contain the Identifier column (identifies an individual in this Instance). On the left side of the screen, you’ll see all the possible items that can be added. Drag and drop them in the main area under Identifier to begin adding them.

 

Items that can be added to the spreadsheet with no additional configuration needed (additional options are available but not required).

• First Name*
• Last Name*
• Username*
• Email
• GGV … The General Global Variable can be used for in-processing and functions as a lookup value. Any values stored are encrypted and cannot be decrypted.
• General Identifier* … Also called ‘Global’ identifier, stored apart from any Instance Identifier, so it’s consistent across all Modules and Instances.
• Role*
• Status
• Status Note
• Password Reset … This field is used to reset passwords for multiple people at a time.

*These items can only be viewed, not edited from the spreadsheet.

 

Items requiring additional information:

• Descriptive … For descriptive data about the selected people or objects.
  • Variable … Start typing, and existing descriptive variables will be displayed. Select an existing one, or add a new one here.
  • Column Label … This will automatically populate based on what is entered as the Variable, but you can change it as needed here.
  • Format … Select the appropriate format of input from the drop down:
    • Text (a number value for Age, for example can be saved as text)
    • Date
    • Time
    • Date and time
  • Picklist items (optional)  … You can create a picklist (a preset drop down list of possible responses) of items to limit the input possibilities and maintain consistent data structure. 
    • If you select an existing variable that has a picklist available, you will be prompted to use that picklist for this question. 
    • Otherwise, separate responses using semi-colons or line breaks. Users will only be able to select one of the available responses.
    • For example, maybe the column is “Year of Eligibility”, and you only want a standard 2 letter response. You could type in FR, SO, JR, SR so the person editing the spreadsheet would select one of those 4 values from the drop down.
  • Click Add.
• Social Media …
  • Type … Select one from the drop down list (all social media platforms that have been enabled are available).
  • Column Label … This is automatically populated, but you can change the displayed label here.
• Score … Add an existing score or create a new one:
  • Use Existing Score … Choose one from the dropdown menu.
    • The Column Label will automatically populate, but you can change the displayed label here.
    • Create New Score … Create a new score variable:
      • Create New Raw Score …
        • Name … Provide a name by clicking in the “Name…” box and following the instructions from the variable name generator.
        • Label … Provide a label (this is the score variable label referenced elsewhere throughout the system).
      • Create New Calculated Score … see Quick Variable Creator
      • Column Label … This automatically populates from the Name, but you can change the displayed label here.
  • • Click Add.
• Rank … Select a Rank from the drop down, then:
  • Column Label … This automatically populates from the Name, but you can change the displayed label here.
  • Rank Format … Determine how the value should be displayed: Default, “x/y” or “x of y”.
• Score Threshold … Use this functionality to set custom thresholds for score variables. This can also be used in conjunction with Objectives for additional customization. The settings include:
  • Type … This sets the threshold type:
    • Criminal … a score value below average but close enough to include for reporting
    • Suspect … a score value just below average
    • Target … the stated achievement level
    • Stretch … above and beyond the target
  • Score … Add an existing score or create a new one:
  • Column Label … This automatically populates from the Score Name, but you can change the displayed label here.
• Time … Add an existing score or create a new one:
  • Use Existing Score … Choose one from the dropdown menu.
    • The Column Label will automatically populate, but you can change the displayed label here.
  • Create New Score … These steps create a new variable and add it to the spreadsheet:
    • Name … Provide a name by clicking in the “Name…” box and following the instructions from the variable name generator.
    • Label … Provide a label (this is the score variable label referenced elsewhere throughout the system).
    • Column Label … This automatically populates from the Name, but you can change the displayed label here.
  • Format … select the time format for display:
    • hh:mm:ss
    • hh:mm
    • hh:ss
  • Click Add.

 

To arrange the order of the columns, click and drag the icon next to the column name.

 

To edit the Column labels or other details for any of the items in your spreadsheet, click the blue … at the right end of the item’s box.

 

When you have finished, click “Publish” to save the spreadsheet and make it active.

 

A spreadsheet can be made Active or Inactive by clicking the gear icon and going to the spreadsheet options.

 

Edit Spreadsheet Configuration

• To edit the properties of an existing spreadsheet, click on the blue down arrow next to the name of the Spreadsheet and click “Edit.”

 

Import an Existing Spreadsheet

To import an existing spreadsheet from another Instance…

• Click on the arrow on the “New Spreadsheet” button and select the drop down “Import Spreadsheet”.
• Specify the Module, Instance, and Spreadsheet you wish to import.
• Click Publish to save it and make it active.

 

= = = = =

Version(s): GideonSoft 2022 Release 3 and later