Jira Cloud for Sheets - Secret notes field

Kate Goldsmith

I've added a Google Sheets "NOTES" column next to the Jira section so that my team can quickly do a daily hot sheet and input internal notes like 'need to follow up with client' or 'switch this ticket to Ben' etc, but with creating a new column if I refresh data those notes will not line up with their corresponding ticket, any suggestions on a fomula that allows you to auto tie cell data to a corresponding ticket number so that it always shifts rows to the correct ticket even after update? I realize I could add a notes column on the jira section but it would still override as empty if I refreshed

Another option is to get the "refreshed" data to come in below verses on top if that makes sense?  

Earl McCutcheon

Hello @Kate Goldsmith ,

Thanks for reaching out and I did a bit of digging on this one for you and found a few logic points to consider that make this a bit difficult, but it is doable using some custom scripting.

First, as you discovered, the inline notes in a google sheet are not intended to be used with dynamic data points as they are tied to the static location of the Cell not the data within the cell,  so adding a note to a cell that contains dynamic data will stay in the cell as the data is updated and shifts to an alternate location within the sheet.  Boil this down and it means that if you enter notes to the dynamic data they get scrambled any time the data updates.

I found the following article that talks about some concepts that I believe will help you out here,  the main idea is to separate the data import page as one sheet of purely dynamic data then use the second sheet in conjunction with importrange() functions and some vlookup() validations to tie a static note and static issue key Row pairs as static columns back to the dynamic data holding onto the reference via that static issue key column as the unique identifier.  Also noting that rather than using an inline note function you would use a new column for notes instead:

• https://infoinspired.com/google-docs/spreadsheet/align-imported-data-with-manually-entered-data/

The next point to take into consideration is how to update the list of issues keys as the dynamic list is updated in sheet1 so that the static values in sheet2 line up correctly.  for this, I would recommend setting up a few app scripts First copy values from sheet1 to sheet 3 on edit, then remove duplicates.

I came up with the following script to append values to the next line on sheet 3 from sheet 1:

function testAppendRows(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var sourceRange = sheet.getRange('A1:A');
  var data = sourceRange.getValues();
  var targetSheet = ss.getSheetByName('Sheet3');
  data.forEach(function(row){
    targetSheet.appendRow(row)
  })
}

Then I found the following article with a really good option to take the results from sheet 3 to remove duplicates.

• https://www.benlcollins.com/spreadsheets/how-to-remove-duplicates-in-google-sheets/#method5

Then to tie this all together you would set Column A1:A on sheet2 = A:A on sheet 3, and this would act as your static list that will update as new issues are added to sheet 1 for alignment mentioned in the first article I mentioned above "Align Imported Data with Manually Entered Data in Google Sheets" so Sheet1 is your dynamic imported data, Sheet 2 is your working document where notes adre added, and sheet 3 is a staging document that is used to align and append updated values between sheets 1 & 2.

Hope this helps get you on the right track.

Regards,
Earl