Upload data from Google Sheets to ALM

After you define requirements, tests, or defects in your spreadsheet, you can upload them to ALM.

Version control considerations

You can upload requirements or tests from Google Sheets to a version control enabled project in ALM.

Consider the following when uploading to a version control enabled project:

  • When you upload a new requirement or test, it is created in ALM, with a checked in status.

  • When you upload an existing requirement or test that is checked in, it is automatically checked out and then checked in during upload. ALM adds a comment to the version history specifying that the requirement or test was updated by upload from Excel.

  • If you upload an existing requirement or test that is checked out by the same user that you use to upload the data, the data is uploaded. If the requirement or test is checked out by another user, the upload fails and an error message is displayed.

For more information on version control, see Version control.

Back to top

Considerations for uploading data to ALM

  • To upload data to ALM, you must first map ALM fields to the Google Sheets spreadsheet columns. You can manually map ALM fields to spreadsheet columns, or the Google Sheets add-on can map fields automatically. For automatic mapping to succeed, the column headings in your spreadsheet must be identical to the field names in ALM.

  • When you upload a new or existing requirement, test, or defect to ALM, it is created with the user name that you use to upload it.

    Example: If you upload a defect to ALM with alex_alm as the user name, the defect’s history shows alex_alm as the person who changed the defect.

  • You must have full create and modify permissions in ALM for the type of record that you are uploading.

    Example: If you are uploading tests, you must have permissions for adding and modifying the test plan tree, design steps, and folders.

Back to top

Step 1: Connect to ALM from the add-on

Connect to your ALM project from the Google Sheets add-on.

  1. Open your Google Sheets spreadsheet.

  2. From the top menu, select Extensions > OpenText ALM Add-on > Start.

  3. In the login page, provide the following information:

    Field Description
    Server Address

    Enter the ALM server address in the following format:

    http://<server name>[:port]/qcbin

    Authentication Method

    Select an authentication method:

    • Username & Password: Log in with your username and password.

    • API Key & Secret: Log in with your API key and API secret.

    Username

    Password

    		 If you select to use username and password to authenticate, enter your username and password.

    Client ID

    Secret

    		 If you select to use API key and secret to authenticate, enter your Client ID and secret.
    Automatically log into my last domain and project Select this if you want to log into your last domain and project after getting authenticated.

    For SSO users: If you already logged into another application with your credentials, you only need to specify the ALM server.

  4. Click Authenticate.

  5. Select the domain and project you want to connect to.

  6. Click Login.

    The add-on page is added in the right panel of your spreadsheet.

Back to top

Step 2: Map data

Map ALM entity fields to Google Sheets columns.

Before mapping data

We recommend you add headers in the first row of your spreadsheet.

Create new mappings

  1. In the right panel of your spreadsheet, under ALM Google Sheets Add-on > Field Mapping, click Mapping.

  2. From the Entity type list, select an entity type (Requirements, Defects, or Tests) for the spreadsheet data.

    • The ALM Field area displays all fields of the selected entity type. The ALM Field - Google Sheets Column mapping area lists the required ALM fields to be mapped.

    • If you select Requirements in Entity type, you can choose to assign a single requirement type to all spreadsheet data.

      Click the Assign single requirement type to all option, and select a requirement type.

  3. To map fields to columns manually, in the ALM Field - Google Sheets Column mapping area, do the following:

    • Specify a Google Sheets column for each required ALM field.

    • To map non-required ALM fields, first move these fields to the ALM Field - Google Sheets Column mapping area, and then specify columns for these fields.

    • If you want to add a header row for your spreadsheet, or you want to update the existing headers with the established mapping, select the Add or update Google Sheets headers with ALM field labels option.

  4. To map fields to columns automatically:

    1. Make sure your spreadsheet has a header row with heading names that are identical to the ALM field names.

    2. Click Auto-map.

    3. In the Specify the row number in Google Sheets where the field labels appear option, enter the row number of the header row.

  5. To save the field mapping in a file for future use, click Save. In the File Name field, specify the name for the file.

    You can use this file to establish mapping next time. See Create mapping using a mapping file.

  6. To apply the mapping to the spreadsheet, click OK.

Create mapping using a mapping file

  1. In the right panel of your spreadsheet, under ALM Google Sheets Add-on > Field Mapping, click Open.

  2. Search for the mapping file you saved. Select the file.

  3. Click Select.

    The Filed Mapping window is auto-filled with the mapping defined in the file.

  4. To apply the mapping to the spreadsheet, click OK.

Back to top

Step 3: Validate data

Once you have mapped your data to the ALM fields, you must check that your data is valid for its mapped field. For example, if the ALM field allows only certain values, your data cannot contain other values.

To validate your data:

  1. In the right panel of your spreadsheet, under ALM Google Sheets Add-on , click Validate Data.

  2. The add-on validates your data against the rules for each ALM field.

    Any invalid cells are marked in red, and an error message is displayed when you put your cursor on the cell.

  3. Correct any invalid cells and click Validate Data again.

Note: If you subsequently add or modify your data, you can validate again, selecting only the modified or added cells or selecting all the cells. If you do not validate prior to uploading your data, this add-on validates your data before uploading.

Back to top

Step 4: Upload data

Upload your Google Sheet data to ALM.

To upload your data:

  1. Select the rows of data to upload.

    • If you do not select rows, all data will be uploaded to ALM.

    • The add-on treats the first row in your spreadsheet as the header row. If the first row is not the header row, this row is not uploaded even when you select it.

  2. In the right panel of your spreadsheet, under ALM Google Sheets Add-on , click Upload Data to ALM.

    Note: If you click Upload Data to ALM without first clicking Validate Data, the add-on validates your data before uploading. Any invalid cells are marked in red, and an error message is displayed when you put your cursor on the cell. If there are no errors, the data is uploaded to ALM.

  3. Refresh the view in ALM. Review the uploaded data and make the necessary additions and adjustments.

Back to top