Enumerates all rows, find current status of Jira task ID and updates it into adjacent cell. - For Google Docs Spreadsheets & App Scripts

jira-tasks-auto-update.gs

// Base64 encoded username:password/pat
const credentials = "dmFpYmhhdnBhbmRleXZwejpjbGVhcmx5X25vdF9zZWNyZXQ=";

const defaults = {
  contentType: "application/json",
  headers: {"Authorization": "Basic " + credentials},
  muteHttpExceptions: true
};

const server = "example.atlassian.net";

const tasks = [5, 7]; // column1, column2 ...
const status = [1, 5]; // row, column

function confirmStatusUpdate() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange(status[0], status[1]);
  const ui = SpreadsheetApp.getUi();
  const result = ui.alert(
    "Do you want to update status of all tasks?",
    ui.ButtonSet.OK_CANCEL);
  if (result == ui.Button.OK) {
    range.setValue("Updating...");
    range.setBackground("#0099e5");
    range.setFontColor("white");
    updateTasks();
  }
  range.setBackground("#34bf49");
  range.setFontColor("white");
  range.setValue("Up to date");
}

function fetchJiraTaskStatus(id) {
  const response = UrlFetchApp.fetch(`https://${server}/rest/api/2/search?jql=issue=${id}`, defaults);
  if (200 == response.getResponseCode()) {
    const json = JSON.parse(response.getContentText());
    if (json?.issues?.length === 1) {
      return json.issues[0].fields.status;
    }
  }
}

function updateTaskStatus(id, row, column) {
  const status = fetchJiraTaskStatus(id);
  if (status) {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const range = sheet.getRange(row, column);
    range.setValue(status.name);
    switch (status.name) {
      case "To Do":
        range.setBackground("#ffd900");
        range.setFontColor("black");
        break;
      case "In Progress":
        range.setBackground("#00bce4");
        range.setFontColor("black");
        break;
      case "Closed":
      case "Resolved":
        range.setBackground("#34bf49");
        range.setFontColor("white");
        break;
    }
  } else {
    Browser.msgBox("Error", `Could not fetch status of task ${id}.`, Browser.Buttons.OK);
  }
}

function updateTasks() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const rows = sheet.getLastRow();
  for (let i = 1; i <= rows; i++) {
    tasks.forEach(x => {
      const id = sheet.getRange(i, x).getValue();
      if (/^[A-Z]+-[\d]+$/.test(id)) {
        updateTaskStatus(id, i, x + 1);
      }
    });
  }
}

This can either be run manually or a trigger can be set for open event.