You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
Next: Root
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
The Atlassian Community can help you and your team get more value out of Atlassian products and practices.
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.
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();
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.