How to build a custom report from Google Sheets

Question

I've googled to find out a way to build reports from data spreadsheets and it seems google data studio and google sheets are an option to achieve that.

Despite some experience on LibreOffice Calc, I am newcomer in Google Sheets and DataStudio.

The data I have in Google Sheets is:

The report I would like to build, whose have to update when new data will arrive in google sheet (let say, each day or so).

I just colored the field names (objId; objDesc; objMore; objProgress; taskId; taskDesc; taskMore; event) and sorted the data into spreadsheet to easy understanding what I want to achieve.

Appreciate your help.

Answer 1

Solution

So basically you want to generate a report of a hierarchy of your objects, tasks and events in the described order whenever you click a button.

In this solution I have provided the code you will need for the most complicated part and then guidance for the rest of the steps.

1. Add a button to your sheet with the data in Insert->Drawing and draw your button.
2. Create a new sheet and insert the following formulas as shown in this picture:

=iferror(ArrayFormula(lookup(unique(Sheet1!A1:A),Sheet1!A1:A,row(Sheet1!A1:A)+1)))

and

=iferror(ArrayFormula(lookup(unique(Sheet1!E2:E),Sheet1!E2:E,row(Sheet1!E2:E)+1)))

These forumulas will basically detect any change in values in the designated columns (in our case the columns for the Object Ids and the Task Ids) which will be helpful for then generating the report.

3. After this head over the script editor and use the following function to generate an array of objects with the right data ordered hierarchically. The following piece of code contains self explanatory comments. This is the most tricky part and takes use of the previously created sheet to know where are our Object and Task Id changing and store the right information in the right place.

For this data:

This will be the generated object after running the function:

 [{ObjectId=1.0, 1={TaskId=S02, Events=[[3242.0]], TaskMore=ppp, Description=ppp}, 0={TaskId=S01, Description=qqq, Events=[[1.0], [23.0], [324.0]], TaskMore=qqq}, ObjectMore=aaaa, ObjectDesc=aaaa, 2={TaskMore=lll, Events=[[3.0]], TaskId=S03, Description=lll}, ObjectProgress=0.7}, {3={Description=www, Events=[[43.0]], TaskMore=www, TaskId=T01}, 5={TaskId=T03, Events=[[5.0]], TaskMore=ttt, Description=ttt}, ObjectMore=bbbb, 4={Description=eee, TaskId=T02, TaskMore=eee, Events=[[54.0], [4.0]]}, ObjectDesc=bbbb, 6={Description=yyy, Events=[[345.0], [343.0]], TaskId=T04, TaskMore=yyy}, ObjectProgress=0.33, ObjectId=2.0}]

function myFunction() {
  // Get sheets
  var sheetData = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var sheetHelp = SpreadsheetApp.getActive().getSheetByName('Sheet2');
  
  // This is the array where we will be storing our info
  var Objects = []
  // Get the ranges of values from our helper sheet that detect changes in that column
  var ObjectIds = sheetHelp.getRange(2, 1,sheetHelp.getLastRow(),1).getValues().flat();
  var TaskIds = sheetHelp.getRange(2, 2,sheetHelp.getLastRow(),2).getValues().flat();
  
  // Delete all the blank elements in the flaten array of values
  ObjectIds = ObjectIds.filter(item => item);
  TaskIds = TaskIds.filter(item => item);
  
  // Delete the last item as this would just be the change between a value and a blank cell
  ObjectIds.pop();
  TaskIds.pop();

  // Lets iterate through all our main objects detected
  for(i=0;i<ObjectIds.length;i++){
    // for each object insert its details and information
    Objects.push({
      ObjectId: sheetData.getRange(parseInt(ObjectIds[i]), 1).getValue(),
      ObjectDesc : sheetData.getRange(parseInt(ObjectIds[i]), 2).getValue(),
      ObjectMore : sheetData.getRange(parseInt(ObjectIds[i]), 3).getValue(),
      ObjectProgress : sheetData.getRange(parseInt(ObjectIds[i]), 4).getValue()   
    });
    // for each object iterate through all its possible tasks
    for(j=0;j<TaskIds.length;j++){
      // get the last row value as for the last element of the tasks if we do not have anything it will 
      // have issues in the following if condition if this is null
      var lastRow = sheetHelp.getLastRow();
      ObjectIds.push(lastRow);
      
      // If the task is within the object index range (i.e for an object between indices 8 and 12
      // his tasks are those between these indices as well
      if(TaskIds[j]<ObjectIds[i+1] && TaskIds[j]>=ObjectIds[i]){
        TaskIds.push(lastRow);
        
        // get all the events for this specific task checking that they are within the indices
        // of the task itself (only events that are within the index boundaries of the task
        var events = sheetData.getRange(TaskIds[j], 8,TaskIds[j+1]-TaskIds[j],1).getValues();
        
        // add the new task object
        Objects[i][j] = {
          TaskId : sheetData.getRange(parseInt(TaskIds[j]),5).getValue(),
          Description : sheetData.getRange(parseInt(TaskIds[j]),6).getValue(),
          TaskMore : sheetData.getRange(parseInt(TaskIds[j]),7).getValue(),
          Events : events
        };
        TaskIds.pop();
      }
      ObjectIds.pop();
    }
  }  
  downloadReport(Objects);
}

4. Go back to the button you created and on its options under Assign Script select our function myFunction.
5. Once you have this array of objects with the right hierarchical information, then it is up to you the way you want to use it for generating reports. You could either:

• Use the array of JSON objects to generate an HTML email for sending reports through Gmail whenever you click the sheet button.
• Generate a new sheet and paste this data with the structure you shown in the picture (much easier to automate now that you have your data sorted), export as PDF and delete this sheet as its purpose was only to generate this PDF report.
• Send it to other services as an array of JSON that then they can easily use.

For the second scenario of this fifth step, I am providing here some guidance as an example of how this could be implemented (a minimal example). Let me know if you need further guidance.

Basically, you would create this new sheet in your spreadsheet and then iterate over our array of objects and over our json objects to set the cell values of our new sheet in their adecuate place. Note that I have also edited the previous code I had to make a call to this new function from myFunction().

The following code has self explanatory comments and it will basically print the object ids that exist in your sheet:

function downloadReport(object){
  // Check if this sheet already exists from generating previous reports
  // and if so delete it to create a new report
  if(SpreadsheetApp.getActive().getSheetByName('Download')){SpreadsheetApp.getActive().deleteSheet(SpreadsheetApp.getActive().getSheetByName('Download'))}
  
  // Create your new sheet
  var sheet = SpreadsheetApp.getActive().insertSheet().setName('Download');
  
  // Iterate over each object in your array of objects
  for(i=0;i<object.length;i++){
  
  // print each object id of your series of objects. For doing so first access the array
  // element and then in that object access the key of the json object. Do the same for the 
  // rest of the fields you are interested to print in your report. 
    sheet.getRange(i+1, 1).setValue(object[i]['ObjectId']);
  } 
}

I hope this has helped you. Let me know if you need anything else or if you did not understood something. :)