Logging Time: A Song as Old as Time

As always occurs in places I work, we are looking to better log how we spend out time¹

I do think it’s good to reflect on how you spend your time as an individual and as an organization. It’s also nice to be able to present aspects of that data with qualitative elements to paint a picture of what you do.² We also want to waste as little time as possible with the actual logging. We’re handling our event-level data via the DLINQ website. We needed a way to do 1-on-1 consultations and email support data.

The desire was to have it in Slack. We use it pretty consistently. I built a Slack-Form-to-WP integration for some other project stuff we do. That seemed excessive for this project. A bit of searching and I found that I could use Slack’s workflow builder to make a simple form in Slack that we could access via /command and that would send data to Google Sheets. Building that was pretty simple.

We also wanted individual people to see what they’d logged for reflection and for when I forgot if I logged something.

To do that, I relied on Google Sheets and Google Script again. The following script builds a new sheet for each person on our list and adds one IMPORTRANGE statement that blends with QUERY³ to get their data.

The script copies a base workbook as the template so it has a couple sheets already waiting. It’s also neat that the script also approves the IMPORTRANGE statement thanks to something I found on StackOverflow a few years ago when I did this at much larger scale.

The bigger picture in these random specific posts is that as you figure out various tools and options, it changes how you think and what you do. It adds up. You have more choices and can do things that save you time and energy. You can choose based on a variety of factors. I’m not sure how that happens if most of the understanding gets outsources into just having an answer you can’t evaluate.

function individualDocMaker() {
  const folderId = '**putthatstringhere**';
  const parentFolder = DriveApp.getFolderById(folderId);//destination folder
  const ssTemplateId ='**putthatstringhere**';
  const template = DriveApp.getFileById(ssTemplateId);//GET TEMPLATE BY ID
  const ss = SpreadsheetApp.getActive();
  const people = ss.getSheetByName('data').getRange("A1:C10").getValues();
  people.forEach((person,index) =>{
    if(person[2] === ''){
      Logger.log(person[0])
        const title = person[0]+ " - Slack Log";
        const newFile = template.makeCopy(title, parentFolder);        
        const newSs = SpreadsheetApp.openById(newFile.getId());
        const sheet = newSs.getSheetByName("logs");
        sheet.getRange('A2').setValue(`=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/**putthatstringhere**","Form Responses!A2:F"), "where Col6='${person[0]}' ")`); 
        addImportrangePermission(newFile.getId())       
        const newEntry = ss.getSheetByName('data').getRange("C"+(index+1)).setValue(newFile.getId());
        Logger.log(newEntry)
        try{
        DriveApp.getFileById(newFile.getId()).addViewer(person[1]);
        } catch{
          Logger.log(person[1]+ ' failed')
        }
       }
    })

    
 
}

//approve the IMPORTRANGE without having to do it manually
function addImportrangePermission(newStudentSheet) {
  // donor or source spreadsheet id
  const donorId = '**putthatstringhere**';
 
  // adding permission by fetching this url
  const url = `https://docs.google.com/spreadsheets/d/${newStudentSheet}/externaldata/addimportrangepermissions?donorDocId=${donorId}`;
 
  const token = ScriptApp.getOAuthToken();
 
  const params = {
    method: 'post',
    headers: {
      Authorization: 'Bearer ' + token,
    },
    muteHttpExceptions: true
  };
   
  UrlFetchApp.fetch(url, params);
}