Format Matrix Question in GoogleSheets

❗️

Deprecated

We have improved matrix exports. Each field/group is now exported as a separate column, streamlining the process for easier data manipulation and insights. Learn More

Currently, when exporting a matrix question type to a spreadsheet, the data is formatted as JSON. JSON is a well-structured and convenient data format that is relatively easy to use, though it does require some basic programming knowledge. We recognize that not all our users may have this level of expertise, so we are offering a workaround to make the export more readable.

📘

Note

This workaround is a temporary measure. We are actively working on enhancing the export format for matrix questions and will release improvements soon.

Implementing the Formatting Function in Google Sheets

1. Open a new Google Sheets file and import the Voiceform .xlsx exported file.

2. In the top menu, find Extensions and click Apps Script.

3. In the Apps Script tab, name the project vf_matrix_formatter.

   

4. Paste the following code snippet:

   JavaScript

   function vf_matrix_formatter() {
     var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
     var range = sheet.getActiveRange();
     var numRows = range.getNumRows();
     var numColumns = range.getNumColumns();
   
     for (var row = 0; row < numRows; row++) {
       for (var col = 0; col < numColumns; col++) {
         var cell = range.getCell(row + 1, col + 1);
         var jsonString = cell.getValue();
   
         var jsonArray;
         try {
           jsonArray = JSON.parse(jsonString);
         } catch (e) {
           Logger.log("Failed to parse JSON in cell " + cell.getA1Notation() + ": " + e.message);
           continue;
         }
   
         var output = [];
   
         // Parse JSON and format the data
         jsonArray.forEach(function(item) {
           var rowTitleOrId = item.row_title || item.row_id;
           if (item.columns && Array.isArray(item.columns)) {
             item.columns.forEach(function(column) {
               var columnTitleOrId = column.group_value?.col_title || column.group_value?.col_id || column.value?.col_title || column.value?.col_id;
               var value = column.group_value?.value || column.value?.value?.value || column.value?.value || "No Value";
               output.push(`${rowTitleOrId}: ${columnTitleOrId} - ${value}`);
             });
           }
         });
   
         if (output.length > 0) {
           var outputString = output.join("\n"); // Join the formatted strings with new lines
           cell.setValue(outputString); // Set the modified text back to the cell
           // Ensure the cell content wraps
           cell.setWrap(true);
         } else {
           Logger.log("No data to output in cell " + cell.getA1Notation());
         }
       }
     }
   }
   

5. Hit the save button and return to your Google Sheets file.

   

6. Go to Extensions -> Macros -> Import Macro and select the vf_matrix_formatter function.

   

Using the Formatter Function

• Select the cell containing the matrix question JSON data.

• Click Extensions -> Macros -> vf_matrix_formatter.

• he JSON cell data will be replaced with a more readable text format. 🎉