CODEWITHSUNDEEP
google-apps-script
Lmo
Lmo

Reputation: 1

How to use Google App Script to locate unique names and add up their valued numbers

So I used an importGoogleCalendar code to pull my workers' names and hours from Google Calendar to Google Sheets. However, I pulled EVEYRTHING. The names are duplicated a numerous amount of times in multiple rows for each day with their corresponding hours. How can I get one unique name for each worker along with their added up hours for each time their name appears on the sheet to another sheet so that it looks cleaner and easier to look at?

Example: I would like

Name Hours
Sam 5
Sam 7
Bob 3
Sam 5
Sam 7
Bob 6
Joe 4

To look like

Name Hours
Sam 24
Bob 9
Joe 4

Here is the code:

function importGoogleCalendar() { 
  var sheet = SpreadsheetApp.getActiveSheet();
  var calendarId = sheet.getRange('B1').getValue().toString(); 
  var calendar = CalendarApp.getCalendarById(calendarId);

// Set filters
  var startDate = sheet.getRange('B2').getValue();
  var endDate = sheet.getRange('B3').getValue();
  var searchText = '';

// Print header
  var header = [["Title", "Description", "Start", "End", "Duration"]];
  var range = sheet.getRange("A6:E6");
  range.setValues(header);
  range.setFontWeight("bold")

// Get events based on filters
  var events = (searchText == '') ? calendar.getEvents(startDate, endDate) : calendar.getEvents(startDate, endDate, {search: searchText});

// Display events 
  for (var i=0; i<events.length; i++) {
    var row = i+7;

    var details = [[events[i].getTitle(), events[i].getDescription(), events[i].getStartTime(), events[i].getEndTime(), '']];

    range = sheet.getRange(row,1,1,5);
    range.setValues(details);

 // Format the Start and End columns
    var cell = sheet.getRange(row, 3);
    cell.setNumberFormat('mm/dd/yyyy hh:mm');
    cell = sheet.getRange(row, 4);
    cell.setNumberFormat('mm/dd/yyyy hh:mm');

 // Fill the Duration column
    cell = sheet.getRange(row, 5);
    cell.setFormula('=(HOUR(D' + row + ')+(MINUTE(D' +row+ ')/60))-(HOUR(C' +row+ ')+(MINUTE(C' +row+ ')/60))');
    cell.setNumberFormat('0.00');
  }
}

I am willing to make another function if need be

Thank You and Stay Safe

Upvotes: 0

Views: 190

Answers (1)

andrewJames
andrewJames

Reputation: 22011

I like the approach of using a pivot table (since they are perfect for handling such data). You get totals, and other features for free.

But if you only want to write summary data to your spreadsheet, you can use the following approach:

My starting point follows on from this line in your existing script:

// Get events based on filters
var events = (searchText == '') ? calendar.getEvents(startDate, endDate) : calendar.getEvents(startDate, endDate, {search: searchText});

From there I pass your events array to a new function:

function summarize(events) {
  var totalsByName = new Map();

  events.forEach((event) => {
    let name = event.getTitle();
    // duration in seconds (from milliseconds / 1000):
    let duration = Math.abs(event.getEndTime() - event.getStartTime()) / 1000.0;
    if (totalsByName.has(name)) {
      // increment the existing duration for this person:
      totalsByName.set(name, totalsByName.get(name) + duration);
    } else {
      // add the first entry for this person:
      totalsByName.set(name, duration);
    }
  } );
  
  // iterate over each entry in the map:
  for (let [name, duration] of totalsByName) {
    console.log(name + ' = ' + (duration / 3600.0));
  }

}

The function populates a map of results - one entry per person's name.

In my example, all I do is print the data to the console.

console.log(name + ' = ' + (duration / 3600.0)); // convert seconds to hours

But you can instead adapt all your existing code to write this data to the spreadsheet instead, using my name and duration values.

You can apply additional logic to sort by names, if you wish, and round the numeric data to a preferred number of decimal places.

Upvotes: 1

Related Questions