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

[Arsal Rana]

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.

[Andrew Samson]

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

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

[Arsalan Rana]

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