Google Spreadsheet conditional formatting for week days check (display weekends with a different color)

Goal: 

I have a column of dates that I want to format so that weekends are in a different color. 

Solution :  

conditional formatting and the formula =OR(WEEKDAY(A1)=7;WEEKDAY(A1)=1) 

JIRA Xporter plugin - tips and tricks

Tips for  the plugin Xporter 

plugin : https://marketplace.atlassian.com/apps/891368/xporter-export-issues-from-jira?hosting=server&tab=overview

• Print the current date
• Count number of issues in a JQL
• Sum a CF for all issues on a JQL
• Number of links
• Number of issues in a JQL (number format)
• Format a number with locale's decimal separator
• SUM Excel correct values in wrong locale format (workaround)
• Iteration on issue & subtasks

Print the current date

tool	Xporter
version	5.0.0
use case	Print the current date

Document generated at %{(new Date()).getDate() + "/" + ((new Date()).getMonth()+1) + "/" + (new Date()).getFullYear()}

Count number of issues in a JQL

tool	Xporter, excel
version	5.0.0
use case	Count number of issues in a JQL

Excel

JQL	<filter>
nfiches1	${jqlcount:
nfiches2	}

usage

=CONCATENATE(nfiches1;<JQL>;nfiches2)

example

${jqlcount:project = PJAB AND issuetype = Pénal AND created >= -90d AND status not in ("Constitution du dossier", "Saisir Avocat") AND Entité = "AXA Banque"}

Sum a CF for all issues on a JQL

tool	Xporter, excel
version	5.0.0
use case	Sum a CF for all issues on a JQL

Excel

JQL	<filter>
CF	<cf name>
nsomme1	${set(count,0)} #{for n=JQLIssuesCount|clause=
nsomme2	} #{if (%{'${JQLIssues[n].
nsomme3	}' .length > 0})} ${set(count,%{${count} + ${JQLIssues[n].
nsomme4	}})} #{end} #{end} %{Number(${count}).toFixed(2)}

usage

in a excel cell :

=CONCATENATE(nsomme1;<JQL>;nsomme2;<CFname>;nsomme3;<CFname>;nsomme4)

example

${set(count,0)} #{for n=JQLIssuesCount|clause=project = PJAB AND issuetype = Pénal AND created >= -90d AND status not in ("Constitution du dossier", "Saisir Avocat") AND Entité = "AXA Banque"}  #{if (%{'${JQLIssues[n].Créance comptable}' .length > 0})}  ${set(count,%{${count} + ${JQLIssues[n].Créance comptable}})}  #{end}  #{end}  %{Number(${count}).toFixed(2)}

Number of links

tool	Xporter, excel
version	5.0.1
use case	Number of links matching a criteria (including creation date)

• Count the links :
  This requires to set a counter, and iterate over the links returned by "LinksCount".

• Filter on a date :
  This requires to create different Date objects, either initiated with the date returned from the issue, or the calculated date. In the end we are indeed comparing milliseconds since 1-1-1970, so the > is enough.

  /*  test if the creation date is within the last 90 days */ (new Date('${dateformat("yyyy-MM-dd HH:mm:ss"):Links[n].Created}') > (new Date(new Date().setDate(new Date().getDate()-90))))

example

/* Set the variable countD4 to the number of links to the current issue "A" where : * link with the current is :  "A" -(est modifié par)-> other issue * linked issue was creted less than 90 days ago * linked issue type is "Créa-Modif Document". */ ${set(countD4,0)} #{for n=LinksCount|filter=%{('${Links[n].LinkType}'.equals('est modifié par')) && (new Date('${dateformat("yyyy-MM-dd HH:mm:ss"):Links[n].Created}') > (new Date(new Date().setDate(new Date().getDate()-90)))) && '${Links[n].IssueTypeName}'.equals('Créa-Modif Document')}} ${set(countD4,%{${countD4}+1})}     * ${Links[n].Key} ${Links[n].Summary} ${Links[n].IssueTypeName}  ${Links[n].Status} #{end} ${countD4}

Number of issues in a JQL (number format)

tool	Xporter, excel
version	5.0.1
use case	Count the number of issues, and print it in excel JQL format

• ${jqlcount:<JQL>} : counts the number of issues returned by the JQL
• %{Number(<...>)} : make sure it's rendered as a number in excel, for example to use it with =SUM() excel function

example

%{Number(${jqlcount:project = PJAB AND issuetype = Pénal AND created >= -92d AND status not in ("Constitution du dossier", "Saisir Avocat") AND Entité = "AXA Banque"})}

Format a number with locale's decimal separator

tool	Xporter
version	?
use case	Format a number with specific locale's decimal separator

• use the ${numberformat("fr","#,##0.00#") function

example

${numberformat("fr","#,##0.00#"):JQLIssues[j].Montant de l'opération (en €)}

SUM Excel correct values in wrong locale format (workaround)

tool	Excel, Xporter
version	5.1.1
use case	Sum values that Xporter printed in the wrong locale.

• Sometime Xporter has some trouble printing numbers that are actually recognized as numbers by excel. An example is by

example

let's say you need to sum values in a locale where the decimal separator is ",", but xporter outputs a decimal separator as "."; hence the =SUM() function will not work.

=SUM(VALUE(CLEAN(SUBSTITUTE(C11;".";",")));VALUE(CLEAN(SUBSTITUTE(C12;".";",")));VALUE(CLEAN(SUBSTITUTE(C13;".";","))))

Iteration on issue & subtasks

tool	Excel, Xporter, ScriptRunner
version	5.1.1
use case	Bulk export, export the issues & insert their subtasks

• One way of doing so is to use the ScriptRunner JQL functions "subtasksOf(<key>)" and iterate on it.

example

#{for j=JQLIssuesCount|clause=project = PRF and issueFunction in subtasksOf("key = ${Key}") and issuetype NOT IN ("Débit", "Crédit") order by updated }