Push Tempo Cloud worklog data into Google Sheets when new worklog is logged

This template demonstrates how to push Tempo Cloud worklog data into Google Sheets when a new worklog is logged in Jira Cloud. Get started to learn more.

What is ScriptRunner Connect?

ScriptRunner Connect is an AI assisted code-first (JavaScript/TypeScript) integration platform (iPaaS) for building complex integrations and automations.

Can I try it out for free?

Yes. ScriptRunner Connect comes with a forever free tier.

Can I customize the integration logic?

Absolutely. The main value proposition of ScriptRunner Connect is that you'll get full access to the code that is powering the integration, which means you can make any changes to the the integration logic yourself.

Can I change the integration to communicate with additional apps?

Yes. Since ScriptRunner Connect specializes in enabling complex integrations, you can easily change the integration logic to connect to as many additional apps as you need, no limitations.

What if I don't feel comfortable making changes to the code?

First you can try out our AI assistant which can help you understand what the code does, and also help you make changes to the code. Alternatively you can hire our professionals to make the changes you need or build new integrations from scratch.

Do I have to host it myself?

No. ScriptRunner Connect is a fully managed SaaS (Software-as-a-Service) product.

What about security?

ScriptRunner Connect is ISO 27001 and SOC 2 certified. Learn more about our security.

📋 Overview

This template demonstrates how to push Tempo worklog data into Google Sheets when a new worklog is logged in Jira Cloud.

🖊️ Setup

• Configure API Connections and Event Listener by creating connectors for Jira Cloud, Tempo Cloud and Google Sheets, or use existing connectors.
• Create a new Google spreadsheet and add the following columns to the first line: Issue Key, Issue Type, User Account ID, Created, Start Date, Start Time, Time Spent Seconds, Billable Seconds, Description
• Once created, copy the spreadsheet ID from the URL (looks like this docs.google.com/spreadsheets/d/1a0tj0P8a7RmWX6q3q0jZCqOrykuWXw3bJ7vpXxl5Vqo/edit?gid=0#gid=0), go to Parameters and add that ID into the SPREADSHEET_ID parameter.

🚀 Usage

Whenever a new worklog is logged via Tempo a new entry should be created in the spreadsheet.

import JiraCloud from './api/jira/cloud';
import GoogleSheets from './api/google/sheets';
import TempoCloud from './api/tempo/cloud';
import { IssueWorklogCreatedEvent } from '@sr-connect/jira-cloud/events';

/**
 * This function collects information about the Tempo worklog that was created and then pushed this information into Google Sheets spreadsheet.
 *
 * @param event Object that holds Worklog Created event data
 * @param context Object that holds function invocation context data
 */
export default async function(event: IssueWorklogCreatedEvent, context: Context<EV>): Promise<void> {
    // Extract the spreadsheet ID from the Environment Variables
    const { SPREADSHEET_ID } = context.environment.vars;

    // Find tempo worklog ID
    const tempoWorklogId = await findTempoWorklogId(+event.worklog.id);

    // Fetch Tempo worklog data
    const tempoWorklog = await TempoCloud.Worklog.getWorklog({
        id: tempoWorklogId.toString()
    });

    // Fetch Jira issue
    const issue = await JiraCloud.Issue.getIssue({
        issueIdOrKey: event.worklog.issueId
    });

    // Fetch the user who created the worklog
    const user = await JiraCloud.User.getUser({
        accountId: tempoWorklog.author.accountId
    });

    // Push the data into spreadsheet
    await GoogleSheets.Spreadsheet.Value.appendValuesInRange({
        spreadsheetId: SPREADSHEET_ID,
        valueInputOption: 'USER_ENTERED',
        range: 'Sheet1!A1:I1',
        body: {
            values: [
                [issue.key, issue.fields.issuetype.name, `${user.displayName} (${user.emailAddress})`, tempoWorklog.createdAt, tempoWorklog.startDate, tempoWorklog.startTime, tempoWorklog.timeSpentSeconds, tempoWorklog.billableSeconds, tempoWorklog.description],
            ],
        }
    });

    console.log(`Tempo worklog pushed: ${tempoWorklog.tempoWorklogId}`);
}

/**
 * Finds tempo worklog ID for Jira worklog ID.
 */
async function findTempoWorklogId(jiraWorklogId: number) {
    const response = await TempoCloud.fetch('/4/worklogs/jira-to-tempo', {
        method: 'POST',
        headers: {
            'Content-Type': 'application/json'
        },
        body: JSON.stringify({
            jiraWorklogIds: [jiraWorklogId]
        })
    });

    if (!response.ok) {
        throw Error(`Unexpected status code while finding out Tempo worklog ID: ${response.status}`);
    }

    const body = await response.json() as FindTempoWorklogIdResponse;

    const worklog = body.results.find(w => w.jiraWorklogId === jiraWorklogId);

    if (!worklog) {
        throw Error(`No matching Tempo worklog found for Jira worklog ID: ${jiraWorklogId}`)
    }

    return worklog.tempoWorklogId;
}

interface FindTempoWorklogIdResponse {
    results: {
        tempoWorklogId: number;
        jiraWorklogId: number;
    }[];
}