Export users from Jira Cloud to Excel file in OneDrive

This template demonstrates how to extract users from Jira Cloud and push them to Excel file in OneDrive, after which the contents of the newly created Excel file is downloaded. 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 push them to Excel file in OneDrive, after which the contents of the newly created Excel file is downloaded.

🖊️ Setup

• Configure API Connections by creating connectors for Jira Cloud and Microsoft, or use existing ones.
• While creating a connector for Microsoft be sure to select self-managed connector and grant Files.Read and Files.ReadWrite permissions for the app.

🚀 Usage

Trigger ExportUsers script manually, after running successfully it should create a new Excel file in OneDrive with all the users found from the Jira Cloud instance, while also downloading the contents of the file and printing it out.

import Microsoft from './api/microsoft';
import JiraCloud from './api/jira/cloud';
import { Convert } from '@sr-connect/convert';

/**
 * At the time of writing this template, MS Graph API did not support creating a new empty excel file in OneDrive directly, so we have to define a empty Excel format ourselves in XLSX format, which is in binary format, and hence convrted to base64.
 * If you wish to use a different default format, create the Excel file in your computer, and then convert the file to base64 format, using one of the online tools, for example: https://www.browserling.com/tools/file-to-base64, and then replace the base64 value here.
 */
const EMPTY_EXCEL_FILE = 'UEsDBAoAAAAIAI1USVfh/jHDSAEAAGgEAAATAAAAW0NvbnRlbnRfVHlwZXNdLnhtbK1UyU7DMBC98xWRr1XilgNCqEkPFI7QQ/kAY08aq97kcbe/Z+IWkFAXofYSK5m3zcT2eLK1plhDRO1dzUbVkBXgpFfaLWr2MX8tH1mBSTgljHdQsx0gmzR34/kuABZEdlizLqXwxDnKDqzAygdwVGl9tCLRa1zwIORSLIDfD4cPXHqXwKUy9RqsGU+hFSuTipctfd4HiWCQFc97YO9VMxGC0VIkqvO1U39cyoNDRcyMwU4HHBCA8aMOfeW0wYH3TpOJWkExEzG9CUsovjV84+Py0/tldV7kSErftlqC8nJliVJhiCAUdgDJmiqvlRXaDS77ZzDyvIxuHORH/0KORP8b9s/rI2SZC4aYdgbw1mPPome2yvpKO+JPo9jQkTrVGkFn0QekoxHh/+197/2eXQYSgpj0b0tnHUn66nlCPysF6og3zxdF8wVQSwMECgAAAAAAjVRJVwAAAAAAAAAAAAAAAAYAAABfcmVscy9QSwMECgAAAAgAjVRJV/KfSdrpAAAASwIAAAsAAABfcmVscy8ucmVsc62SwU7DMAxA73xF5PuabkgIoaW7IKTdJjQ+wCRuG7WNo8SD7u+JkEAMjWkHjnHs52fL6808jeqNUvYcDCyrGhQFy86HzsDL/mlxDyoLBocjBzJwpAyb5mb9TCNKqcm9j1kVSMgGepH4oHW2PU2YK44Uyk/LaUIpz9TpiHbAjvSqru90+smA5oSpts5A2rolqP0x0jVsbltv6ZHtYaIgZ1r8yihkTB2JgXnU75yGV+ahKlDQ511W17v8PaeeSNChoLacaBFTqU7iy1q/dRzbXQnnz4xLQrf/uRyahYIjd1kJY/wy0ic30HwAUEsDBAoAAAAAAI1USVcAAAAAAAAAAAAAAAADAAAAeGwvUEsDBAoAAAAAAI1USVcAAAAAAAAAAAAAAAAJAAAAeGwvX3JlbHMvUEsDBAoAAAAIAI1USVdO42461wAAACwCAAAaAAAAeGwvX3JlbHMvd29ya2Jvb2sueG1sLnJlbHOtkU2LwjAQhu/+ijB3m1ZhWRZTLyL0Kt0fENLpB22TkBl1++83WtAVZPHgKbwT5nkfks32ZxzECQN1zirIkhQEWuOqzjYKvsv98hMEsbaVHpxFBRMSbPPF5oCD5rhDbedJRIglBS2z/5KSTIujpsR5tPGmdmHUHGNopNem1w3KVZp+yPCXAfkDUxSVglBUGYhy8vgK29V1Z3DnzHFEy08qJPE0RH9R6tAgK5hzEjkgn9ev3lnPcRfv7dc4D7P/HNbvdDi70FOLyHeP2yg+0OW4yciHT85/AVBLAwQKAAAAAACNVElXAAAAAAAAAAAAAAAADgAAAHhsL3dvcmtzaGVldHMvUEsDBAoAAAAIAI1USVd3VIKwlQEAAOYCAAAYAAAAeGwvd29ya3NoZWV0cy9zaGVldDEueG1sjZJNb9swDIbv+xWC7o3tLGlnI05RLChaYAOGtcXOikzbQiVRkOi22a8fo3wARXfojS9JPfzS6vrNWfECMRn0raxmpRTgNXbGD618ery9+CZFIuU7ZdFDK3eQ5PX6y+oV43MaAUgwwKdWjkShKYqkR3AqzTCA50iP0SliGYcihQiqy4+cLeZleVk4Zbw8EJr4GQb2vdGwQT058HSARLCKuP00mpBONKc/g3MqPk/hQqMLjNgaa2iXoVI43dwPHqPaWh77rVoofWJn8QHvjI6YsKcZ446Nfpy5LuqCSetVZ3iC/dZFhL6VN1VzU8livcq5t7nHX1F00KvJ0m98vQMzjMQnWkqBE1nj4Qe8gOVQK8v3vu9osy832nS7DSTN62rlcnkusVGk2A5qgJ8qDsYnYaHnnHJ2JUU8FMs2YcgWF94iEbqTGnk0iHv1VYoekU7iyH0AmoLAaLh2vlArA0aKyhA/Zv9f5IDdBNPKxbxe1JdX85q5/B3J6P8EEjv5GFXJs/WGHvGP6Whkx1Ged7TvoDh/0fU/UEsDBAoAAAAAAI1USVcAAAAAAAAAAAAAAAAJAAAAeGwvdGhlbWUvUEsDBAoAAAAIAI1USVd2mzDfIQYAABkfAAATAAAAeGwvdGhlbWUvdGhlbWUxLnhtbO1ZTW/bNhi+71cQurfyl1InqFPEjt1ubdogcTv0SEu0xIYSBZJO4tvQHgcMGNYNuwzYbYdhW4EW2KX7Ndk6bB3Qv7BX1ocpm2qcJt1QIDk4IvU87xff9yVpX79xHDJ0SISkPOpY9as1C5HI5R6N/I51fzi40raQVDjyMOMR6VhTIq0bmx9dxxsqICFBQI/kBu5YgVLxhm1LF6axvMpjEsG7MRchVjAUvu0JfARiQ2Y3arU1O8Q0slCEQ5B6bzymLkHDRKS1iXLpfQYfkZKzGZeJfXemU+ekaO+gPvsvp7LHBDrErGOBLo8fDcmxshDDUsGLjlWb/Vk2oO05jakqukYdzP5yak7xDhopVfijglsftNavbc+1NDItBmi/3+/163OpKQS7LvhdX4a3Bu16t5Csw9Jng4Zezam1Fii6luYyZb3b7TrrZUpTo7SWKe3aWmurUaa0NIpj8KW71eutlSmORllbpgyura+1FigpLGA0OlgmJKs9X7Q5aMzZLTOjDYx2kSEaztZSMJMRqcqMDPEjLgaASJceKxohNY3JGLuA7OFwJCieacEbBGuvsjlXLs8lCpF0BY1Vx/okxlA+c8yblz+9efkcvXn57OTxi5PHv548eXLy+BcT8xaOfJ35+ocv//nuM/T38+9fP/26giB1wh8/f/77b19VIJWOfPXNsz9fPHv17Rd//fjUhN8SeKTjhzQkEt0lR2iPh+CfSQUZiTNShgGmJQoOAGpC9lVQQt6dYmYEdkk5hg8EtAsj8ubkUcne/UBMFDUhbwdhCbnDOetyYfbpdqJO92kS+RX6xUQH7mF8aFTfW1jl/iSG3KZGob2AlEzdZbDw2CcRUSh5xw8IMfEeUlqK7w51BZd8rNBDirqYmgMzpCNlZt2iISzQ1GgjrHopQjsPUJczo4JtcliGQoVgZhRKWCmaN/FE4dBsNQ6ZDr2DVWA0dH8q3FLgpYJF9wnjqO8RKY2ke2JaMvk2hjZlzoAdNg3LUKHogRF6B3OuQ7f5QS/AYWy2m0aBDv5YHkDGYrTLldkOXq6ZZAwLgqPqlX9AiTpjsd+nfmBOluTNRBhrhPByjU7ZGJMo3wTKvTyk0Vs7O6PQ2i87+0Jn34LtzlhRi/28EviBdvFtPIl2CVTKZRO/bOKXTfxtFf4+WrfWrG39yJ5KCqsP8GPK2L6aMnJHpp1egpveAGbT0YxX3BriAB5zpWWkL/BsgARXn1IV7Ac4Bl31VI0vc/m+RDGXcGWxqhWkV2MK/s8mneIyC3isdriXzjdLt9xCUjr0ZUldMxGyusrmtfOrrKfYlXXWnQqdzmk6bT3AUFsIJ19r1NcaqQWQRZgRL1mMTEi+WO975eo1fekC7BHTvOZrvfn+4uuc0ZaLi3vNEHfbUHssWhiio4617jQcC7k47lhjOIbBYxiDTJk0KMz8qGO5KvN1hdpd9H69IunqNafa+bKeWEi1jWWQEmfvii96Is2RhtNKgnJRnhi70Kq2NNv1/90We2nByXhMXFU1pY3zt3yiiNgPvCM0YhOxh8GDVpp6HpWwbTTygYD0b2VZWS7zvIAWv07KKwuzOMBZQbT1lEgJ6aCwIx3qRtpVPryzT80L9cm59Cnf+V04Eze92bMLBwWBUZLCHYsLFXBoXXFA3YGAs0WqEexDUDqJaYglX6snNpNDrd2lUrLu6Adqj/pIUGiRKhCE7KrM49Pk1RulXTcXlbemudUyzh5G5JCwYVLoa0kwLBTk7SePSopcWkjbWIQjf/ABHJNa77yPzdW1zraltvTdQ9tU1s9vyWq7u6a0UeF+w3nLTra8jcdw9UHJB+wAVLhMOycP+R5kBiqOEghy9Uo7K9ZicgS2t3U/E2H/7bGrXZUJF3561eLfrIr/qUrPE3/HEH7n1Ojbhpq2tYtSOlz+cY6PHoEF23AJm7BsSsYwzJ52Rer+iHvT/JnJtJdkgSk2CBbtkTGi3nGx5AtRzn71mh8Z9jI9SSgKbnMVbsbQNqaC31iFX3A284tpwZ/dPI0ymKY/ZWQZMG+189ix6NxRXMmTiiia83z1KK60gu8URXV8ahTz2NnG/CTHSuBe/osepLqtJffmv1BLAwQKAAAACACNVElXBTuAXnYCAAADBgAADQAAAHhsL3N0eWxlcy54bWyllF1vmzAUhu/3KyzfUwMNLImAammKVKmbKjWTduuASaz6AxnTkU377zsGEhJ12qb2ysevj5/z+jO56aRAL8w0XKsUB1c+RkwVuuRql+Kvm9ybY9RYqkoqtGIpPrAG32QfksYeBHvaM2YREFST4r219ZKQptgzSZsrXTMFI5U2klromh1pasNo2bhJUpDQ92MiKVd4ICxl8T8QSc1zW3uFljW1fMsFt4eehZEslvc7pQ3dCnDaBTNaoC6ITXis0EuvikheGN3oyl4BlOiq4gV77XVBFoQWEwmwbyMFEfHDYeFZUmllG1ToVlnYfaA7h8tnpb+r3A05ccjKkkILbZCFUszJBHQquTigFypSHDqhN8IGQXLYil78MQhBP0fRY8ItFXxruBPJUKFvGuByIU6uQjwIWQIbbplROXTQGG8ONZhRcDUGTJ/3j+ydoYcgjM4m9A3U3WpTwlWc9uMoZYlglYUJhu/2rrW6Jm7QWtjoLCk53WlFhUMeZ4wBYAsmxJO7r9+qC3ZXIdXKXNr7MsVw8d3qjyEYGsMBM3Qc/5w2sN+NRV11yT+h+0IX9JOK3Emm+It7G2JCoG3LheXqD4aBWXaT137UusdyWQUYJatoK+zmNJjiKf7MSt7K8JT1yF+0HbOm+MGdVBC7GqyzD43tW9QanuKfd6uPi/VdHnpzfzX3Ztcs8hbRau1Fs9vVep0v/NC//XX2at/xZseHBpBlIyDLjIsdzT9NWorPOoP9fv/A9rn3RRj7n6LA9/JrP/BmMZ178/g68vIoCNfxbHUX5dGZ9+iNv4RPgmAyHy0tl0xwxS7tb85VOCTo/mUR5HgSZPq+s99QSwMECgAAAAAAjVRJVwAAAAAAAAAAAAAAAAkAAABkb2NQcm9wcy9QSwMECgAAAAgAjVRJV+7UeraAAQAAIgMAABAAAABkb2NQcm9wcy9hcHAueG1snVJBbtswELz3FQLvMeW0CAqDYlA4LXJoUAO20/OWWllEKJLgbgS7ry8lw4rc9NTb7OxgNFqOuj92rugxkQ2+EstFKQr0JtTWHyqx3327+SwKYvA1uOCxEickca8/qE0KERNbpCI7eKpEyxxXUpJpsQNa5LXPmyakDjiP6SBD01iDD8G8duhZ3pblncQjo6+xvomToTg7rnr+X9M6mCEfPe9OMftp9SVGZw1w/kn9ZE0KFBouvh4NOiXnS5WNtmhek+WTLpWcj2prwOE6G+sGHKGSb4R6RBhutgGbSKueVz0aDqkg+ztf7VYUv4BwiFOJHpIFz+IsOw8jdpE46Z8hvVCLyKTkRI5wrp1j+0kvR0EG10I5Bcn4OuLOskP60Wwg8T8SL+eJxwxilnFPuTHv4l0+9Jf1OnQRfL6fnNATeDjgoJ3Qd+tfaB934QEYLwe+JtW2hYR1fpPpASZCPeakyQ36dQv+gPVF834x1OH5XHm9vFuUH8tybMGFU/Kt3foPUEsDBAoAAAAIAI1USVfbPrVKXwEAAOMCAAARAAAAZG9jUHJvcHMvY29yZS54bWydUstOwzAQvPMVke+Jk0YqEKWpBKgnKiFRBOJm7G1rmtiWvSXN3+M8mraiJ247O7PjfTifH6oy+AHrpFYzkkQxCUBxLaTazMjbahHekcAhU4KVWsGMNODIvLjJucm4tvBitQGLElzgjZTLuJmRLaLJKHV8CxVzkVcoT661rRh6aDfUML5jG6CTOJ7SCpAJhoy2hqEZHclgKfhoafa27AwEp1BCBQodTaKEnrQItnJXCzrmTFlJbAxclR7JUX1wchTWdR3VaSf1/Sf0Y/n82o0aStWuigMpcsEzboGhtsWb2ildq5ye5VoeJZZQdOkh9JHbf30Dxz49Ah8LcNxKg/5OPXmR8OfYQVNrK5xnL1B7KYaw0bbpqRPyoGQOl/7cawnioTn1+pfKh932M4AI/E6yfoNH5j19fFotSDGJJ2mYxGF8v0riLJ1mk9vPtueL+pNhNTzyb8ejwTDfxb8sfgFQSwMECgAAAAgAjVRJV8cEf4xXAQAAcAIAAA8AAAB4bC93b3JrYm9vay54bWyNksluwjAQQO/9Cst3cAIttBEJUtVW4lJxgN6NMyEW3mQ7LH/fwSGVKi5cbI+XN29sL5ZnrcgRfJDWlDQfZ5SAEbaWZl/S7eZr9EpJiNzUXFkDJb1AoMvqaXGy/rCz9kDwvAklbWN0BWNBtKB5GFsHBlca6zWPGPo9C84Dr0MLELVikyybMc2loT2h8I8wbNNIAR9WdBpM7CEeFI9oH1rpwkDT4hGc5v7QuZGw2iFiJ5WMlwSlRItitTfW853Cqs/5y0DG4R1aS+FtsE0cI+omeVdvnrE870uuFo1U8NPfOuHOfXN9zaIoUTzEz1pGqEuKOZU9wb8J37n3TioM3qbZlLLq7yXWntTQ8E7FDVoNdHzT2XOW55Rgygh+7eWRiwtOX88mu3DrSWpX9XWNmGS0DUhJHyBidJRB4nWgRCFxl1/VSYANFMGVQItrlyjzPJvM047BsfoFUEsBAhQACgAAAAgAjVRJV+H+McNIAQAAaAQAABMAAAAAAAAAAAAAAAAAAAAAAFtDb250ZW50X1R5cGVzXS54bWxQSwECFAAKAAAAAACNVElXAAAAAAAAAAAAAAAABgAAAAAAAAAAABAAAAB5AQAAX3JlbHMvUEsBAhQACgAAAAgAjVRJV/KfSdrpAAAASwIAAAsAAAAAAAAAAAAAAAAAnQEAAF9yZWxzLy5yZWxzUEsBAhQACgAAAAAAjVRJVwAAAAAAAAAAAAAAAAMAAAAAAAAAAAAQAAAArwIAAHhsL1BLAQIUAAoAAAAAAI1USVcAAAAAAAAAAAAAAAAJAAAAAAAAAAAAEAAAANACAAB4bC9fcmVscy9QSwECFAAKAAAACACNVElXTuNuOtcAAAAsAgAAGgAAAAAAAAAAAAAAAAD3AgAAeGwvX3JlbHMvd29ya2Jvb2sueG1sLnJlbHNQSwECFAAKAAAAAACNVElXAAAAAAAAAAAAAAAADgAAAAAAAAAAABAAAAAGBAAAeGwvd29ya3NoZWV0cy9QSwECFAAKAAAACACNVElXd1SCsJUBAADmAgAAGAAAAAAAAAAAAAAAAAAyBAAAeGwvd29ya3NoZWV0cy9zaGVldDEueG1sUEsBAhQACgAAAAAAjVRJVwAAAAAAAAAAAAAAAAkAAAAAAAAAAAAQAAAA/QUAAHhsL3RoZW1lL1BLAQIUAAoAAAAIAI1USVd2mzDfIQYAABkfAAATAAAAAAAAAAAAAAAAACQGAAB4bC90aGVtZS90aGVtZTEueG1sUEsBAhQACgAAAAgAjVRJVwU7gF52AgAAAwYAAA0AAAAAAAAAAAAAAAAAdgwAAHhsL3N0eWxlcy54bWxQSwECFAAKAAAAAACNVElXAAAAAAAAAAAAAAAACQAAAAAAAAAAABAAAAAXDwAAZG9jUHJvcHMvUEsBAhQACgAAAAgAjVRJV+7UeraAAQAAIgMAABAAAAAAAAAAAAAAAAAAPg8AAGRvY1Byb3BzL2FwcC54bWxQSwECFAAKAAAACACNVElX2z61Sl8BAADjAgAAEQAAAAAAAAAAAAAAAADsEAAAZG9jUHJvcHMvY29yZS54bWxQSwECFAAKAAAACACNVElXxwR/jFcBAABwAgAADwAAAAAAAAAAAAAAAAB6EgAAeGwvd29ya2Jvb2sueG1sUEsFBgAAAAAPAA8AhAMAAP4TAAAAAA=='

/**
 * When triggered, this function fetches all the users from Jira Cloud, picks out 4 fields for each user, then pushes those fields into Excel file in OneDrive, 
 * downloads the file for example sake and finally prints out the name of the generated file and the download URL.
 */
export default async function (event: any, context: Context): Promise<void> {
    // Get all users from Jira
    console.log('Fetching users...')
    const users = await getUsers();
    console.log(`Found ${users.length} users.`);

    // Set a name for the Excel sheet
    const fileName = `Users-${Date.now()}.xlsx`;

    // Create empty Excel file in OneDrive
    const excelFile = await createExcelFile(fileName, Convert.base64ToBuffer(EMPTY_EXCEL_FILE));

    // Construct the user data to be uploaded to previously generated empty Excel file
    const content = [
        ['Account ID', 'Email', 'DisplayName', 'Active'], 
        ...users.map(user => [user.accountId, user.email, user.displayName, user.active])
    ];

    // Push users data to previously generated Excel file
    await writeContentIntoExcelFile(excelFile.id, 'Users', `A1:D${users.length + 1}`, content);

    // Download the user data from the Excel file for example sake
    const generatedExcelContent = await fetchExcelContent(excelFile.id, 'Users');
    // Print out the downloaded content
    console.log('Excel content: ', generatedExcelContent);

    // Finally print out the name of the generated file and a download URL
    console.log(`Users exported into file: ${fileName} - Download URL: ${excelFile['@microsoft.graph.downloadUrl']}`);
}

async function createExcelFile(fileName: string, content: ArrayBuffer) {
    const response = await Microsoft.fetch(`/v1.0/me/drive/root:/${encodeURIComponent(fileName)}:/content`, {
        method: 'PUT',
        headers: {
            'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        },
        body: content
    });

    if (!response.ok) {
        throw Error(`Unexpected response code while creating new Excel file: ${response.status} - ${await response.text()}`);
    }

    return await response.json<CreateExcelFileResponse>();
}

async function writeContentIntoExcelFile(fileId: string, sheetName: string, range: string, data: any[][]) {
    const response = await Microsoft.fetch(`/v1.0/me/drive/items/${fileId}/workbook/worksheets('${encodeURIComponent(sheetName)}')/range(address='${range}')`, {
        method: 'PATCH',
        headers: {
            'Content-Type': 'application/json'
        },
        body: JSON.stringify({
            values: data
        })
    });

    if (!response.ok) {
        throw Error(`Unexpected response code while writing content into Excel file: ${response.status} - ${await response.text()}`);
    }
}

async function fetchExcelContent(fileId: string, sheetName: string) {
    const response = await Microsoft.fetch(`/v1.0/me/drive/items/${fileId}/workbook/worksheets('${sheetName}')/usedRange`);

    if (!response.ok) {
        throw Error(`Unexpected response code fetching Excel file as CSV: ${response.status} - ${await response.text()}`);
    }

    return await response.json();
}

// 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;
}

interface CreateExcelFileResponse {
    id: string;
    '@microsoft.graph.downloadUrl': string;
}