Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,462,871
Community Members
 
Community Events
176
Community Groups

Why can't I use data from my Google spreadsheet when creating Jira issues using REST API

Edited

Summary

I'm trying to write a script in Google Sheets that allows me to create issues in Jira Cloud using REST API whenever I edit a particular cell. In addition, the Jira issue should contain information that I pulled from my spreadsheet.

I managed to get the trigger to work and got a successful response from the Jira API. I can successfully create issues but only with issue field information that I put into the script. As soon as I try to use data from my spreadsheet, it doesn't work anymore.

I looked if there are any related questions on stackoverflow but couldn't find any that addressed this particular issue.

Description

An issue should be created as soon as I edit a specific cell in my spreadsheet and I'm using the specialOnEdit() function which allows me to target the cell that is currently located in the last row containing any values and column 6. My code looks like this

function specialOnEdit(evt) {
  var range = evt.range;
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('name of my sheet');
  var lastRow = sheet.getLastRow();

  if(range.getRow() == lastRow && range.getColumn() == 6) {
   createIssue()
  }
}

The createIssue() function contains my code for connecting to the Jira API. It looks like this

function createIssue() {
  
    var url = "https://xxxxx.atlassian.net/rest/api/3/issue";
    var username = "xxxxx";
    var password = "xxxxx";
    var userCredentials = "Basic " + Utilities.base64Encode(username + ":" + password);

    var data = {
      "fields": {
        "summary": "summary",
        "issuetype": {
          "id": "id",
        },
        "project": {
          "id": "id"
        },
        "priority": {
          "id": "id",
        }
      }
    };
      var payload = JSON.stringify(data);

      var headers = { "Accept":"application/json",
                  "Content-Type":"application/json",
                  "Authorization": userCredentials,
                  "muteHttpExceptions": "True",
      };
      var options = { "method":"POST",
                    "headers": headers,
                    "payload": payload,
      };
      var response = UrlFetchApp.fetch(url, options);
}

So far so good, everything works. If I edit the target cell, a Jira issue is being created containing the information I put into the respective "fields":.

Now I want to use cell values from my Google Sheet to populate those "fields":; the values being again from my last row that contains any values and this time column 1. However, if I start to declare variables for that in my createIssue() function the script doesn't work anymore. What I tried looks like this

function specialOnEdit(evt) {
  var range = evt.range;
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('name of my sheet');
  var lastRow = sheet.getLastRow();

  if(range.getRow() == lastRow && range.getColumn() == 6) {
   createIssue()
  }
}

function createIssue() {

    var url = "https://xxxxx.atlassian.net/rest/api/3/issue";
    var username = "xxxxx";
    var password = "xxxxx";
    var userCredentials = "Basic " + Utilities.base64Encode(username + ":" + password);

    var sheet = SpreadsheetApp.getActiveSpreadSheet().getSheetByName('name of my sheet');
    var lastRow = sheet.getLastRow();
    var summary = sheet.getRange(lastRow, 1);

    var data = {
      "fields": {
        "summary": summary,
        "issuetype": {
          "id": "id",
        },
        "project": {
          "id": "id"
        },
        "priority": {
          "id": "id",
        }
      }
    };
      var payload = JSON.stringify(data);

      var headers = { "Accept":"application/json",
                  "Content-Type":"application/json",
                  "Authorization": userCredentials,
                  "muteHttpExceptions": "True",
      };
      var options = { "method":"POST",
                    "headers": headers,
                    "payload": payload,
      };
      var response = UrlFetchApp.fetch(url, options);
}

If trigger the specialOnEdit() function, nothing happens but I don't get any error message in the console either. Does anyone know what seems to be the problem? Or is this simply not possible?

*edit If I add a variable using a text string like so var summary = "example string" it works just fine. As soon as I add any variables related to my spreadsheet the function doesn't work anymore regardless whether I fix the getValue() mistake as pointed out by Cooper.

In theory only adding something like

var sheet = SpreadsheetApp.getActiveSpreadSheet().getSheetByName('name of my sheet');

without anything else should not have any effect but it shouldn't mess with my function either which it does. Might be an important detail.

2 answers

0 votes

You did some wrong code I see. You should follow this: 

var summary = sheet.getRange(lastRow, 1).getValue();

var summary = sheet.getRange(lastRow, 1); should be var summary = sheet.getRange(lastRow, 1).getValue();

Suggest an answer

Log in or Sign up to answer