Export users from Jira Cloud to Google Sheets

This template demonstrates how to extract users from Jira Cloud, and then how to push that data into Google Sheets. 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 extract users from Jira Cloud, and then how to push that data into Google Sheets.

🖊️ Setup

Set up connectors for Jira Cloud and Google Sheets. You can either create new connectors or use existing ones.

🚀 Usage

Run the script and check the results.

• Manually trigger the ExportUsers script.
• When successful, a new sheet containing all the users from the Jira Cloud instance will be created in Google Sheets.

import JiraCloud from "./api/jira/cloud";
import GoogleSheets from "./api/google/sheets";

/**
 * This function finds all users from Jira Cloud and exports them Google Sheets
 * Run this script manually any time you would like to have users exported to Google Sheets
 */
export default async function (event: any, context: Context): Promise<void> {
    // Get all users from Jira
    const users = await getUsers();

    // Set a name for the sheet
    const sheetName = `Exported Users ${new Date().toUTCString()}`;

    // Create a new sheet
    const sheet = await GoogleSheets.Spreadsheet.createSpreadsheet({
        body: {
            properties: {
                title: `Exported Users ${new Date().toUTCString()}`
            },
        }
    });

    // Append users into the sheet
    await GoogleSheets.Spreadsheet.Value.appendValuesInRange({
        spreadsheetId: sheet.spreadsheetId,
        valueInputOption: 'USER_ENTERED',
        range: 'Sheet1!A1:D1',
        body: {
            values: [
                ['Account ID', 'Email', 'DisplayName', 'Active'],
                ...users.map(user => [user.accountId, user.email, user.displayName, user.active])
            ],
        }
    });

    // And print out how many users were exported and what's the name of the sheet that was created
    console.log(`Jira users (${users.length}) imported into sheet: ${sheetName}`);
}

// Function to get all users from Jira (traverses all paginated responses until all users have been found)
async function getUsers() {
    const allUsers: User[] = [];
    const maxResults = 50;
    let startAt = 0

    do {
        const users = await JiraCloud.User.getUsers({
            startAt,
            maxResults
        });

        for (const user of users) {
            allUsers.push({
                accountId: user.accountId ?? 'N/A',
                displayName: user.displayName ?? 'N/A',
                email: user.emailAddress ?? 'N/A',
                active: user.active ?? true
            });
        }

        if (users.length === maxResults) {
            startAt += maxResults;
        } else {
            startAt = 0
        }
    } while (startAt > 0);

    return allUsers;
}

// Type to hold user data in
interface User {
    accountId: string;
    email: string;
    displayName: string;
    active: boolean;
}