Autogenerating report summary text using Google Sheets

After 8 years working in a marketing company I came to the conclusion that writing monthly reports for management can get tedious - it's absolutely necessary to report KPI's to management but so much of the actual writing is just based on rules and can be susceptible to copy and paste errors. Thanks to the wonders of Google Sheets much of a summary statement for a report can be autogenerated via simple 'if' and formulas statements.

Formula to output the name and year of last month (i.e. the last full month before the month that you are in):

=text(EOMONTH(TODAY(),-2)+1,"MMMM yyy")

Formula to output the name and year of the month before last:

=text(EOMONTH(TODAY(),-3)+1,"MMMM yyy")

Formula for formatting a number into comma separated text:

=text(A1,"#,###")

Formula for stating if this month has seen an increase or decrease in traffic:

=if(A2>A1,"increase","decrease")

Formula for showing the month-on-month change as a percentage (and only show this as a positive to tie in with the increase/decrease statement before): =text(abs(((A2-A1)/A1)),"#%")

Extract the domain name from a full URL/page path:

Source

function getDomainName (urlRange) {
  urlRange = strToArray(urlRange);
  var regex, returnValue = [];
  for (var i = 0; i < urlRange.length; i++) {
    url = urlRange[i];
    regex = new RegExp(/((www)\.)?.*(\w+)\.([\w\.]{2,6})/);
    returnValue.push([regex.exec(url)[0]
                     .replace(/^http(s)?:\/\//i, "")
                     .replace(/^www\./i, "")
                     .replace(/\/.*$/, "")]);
  }
  return returnValue;
}

function strToArray(obj) {
  if (typeof obj == "string") {
    return [obj];
  } else {
    return obj;
  }
}

Delete all sheets apart from the one with the Universal Analytics Report Configuration:

function DeleteAllSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for (i = 0; i < sheets.length; i++) {
    switch (sheets[i].getSheetName()) {
      case "Report Configuration":
        break;
      default:
        ss.deleteSheet(sheets[i]);
    }
  }
}

Screaming Frog regex to extract data attribute values

data-ATTRIBUTE="([^"]+)"

Concatenate the contents from multiple cells into one cell with line breaks:

=ArrayFormula(CONCATENATE(A1:A4 & CHAR(10)))

Formula to find all of the questions people asked which led to your site (Google Search Console filter)

^who|^what|^where|^why|^when|^how|^is|^are|^does|^do|^can

Google Data Studio: ISO Language Codes to Language Names

    CASE
    WHEN REGEXP_MATCH( Language, '^aa.*') THEN "Afar"
    WHEN REGEXP_MATCH( Language, '^ab.*') THEN "Abkhazian"
    WHEN REGEXP_MATCH( Language, '^ae.*') THEN "Avestan"
    WHEN REGEXP_MATCH( Language, '^af.*') THEN "Afrikaans"
    WHEN REGEXP_MATCH( Language, '^ak.*') THEN "Akan"
    WHEN REGEXP_MATCH( Language, '^am.*') THEN "Amharic"
    WHEN REGEXP_MATCH( Language, '^an.*') THEN "Aragonese"
    WHEN REGEXP_MATCH( Language, '^ar.*') THEN "Arabic"
    WHEN REGEXP_MATCH( Language, '^as.*') THEN "Assamese"
    WHEN REGEXP_MATCH( Language, '^av.*') THEN "Avaric"
    WHEN REGEXP_MATCH( Language, '^ay.*') THEN "Aymara"
    WHEN REGEXP_MATCH( Language, '^az.*') THEN "Azerbaijani"
    WHEN REGEXP_MATCH( Language, '^ba.*') THEN "Bashkir"
    WHEN REGEXP_MATCH( Language, '^be.*') THEN "Belarusian"
    WHEN REGEXP_MATCH( Language, '^bg.*') THEN "Bulgarian"
    WHEN REGEXP_MATCH( Language, '^bh.*') THEN "Bihari languages"
    WHEN REGEXP_MATCH( Language, '^bi.*') THEN "Bislama"
    WHEN REGEXP_MATCH( Language, '^bm.*') THEN "Bambara"
    WHEN REGEXP_MATCH( Language, '^bn.*') THEN "Bengali"
    WHEN REGEXP_MATCH( Language, '^bo.*') THEN "Tibetan"
    WHEN REGEXP_MATCH( Language, '^br.*') THEN "Breton"
    WHEN REGEXP_MATCH( Language, '^bs.*') THEN "Bosnian"
    WHEN REGEXP_MATCH( Language, '^ca.*') THEN "Catalan; Valencian"
    WHEN REGEXP_MATCH( Language, '^c$') THEN "Likely bot traffic"
    WHEN REGEXP_MATCH( Language, '^ce.*') THEN "Chechen"
    WHEN REGEXP_MATCH( Language, '^ch.*') THEN "Chamorro"
    WHEN REGEXP_MATCH( Language, '^co.*') THEN "Corsican"
    WHEN REGEXP_MATCH( Language, '^cr.*') THEN "Cree"
    WHEN REGEXP_MATCH( Language, '^cs.*') THEN "Czech"
    WHEN REGEXP_MATCH( Language, '^cu.*') THEN "Church Slavic; Old Slavonic; Church Slavonic; Old Bulgarian; Old Church Slavonic"
    WHEN REGEXP_MATCH( Language, '^cv.*') THEN "Chuvash"
    WHEN REGEXP_MATCH( Language, '^cy.*') THEN "Welsh"
    WHEN REGEXP_MATCH( Language, '^da.*') THEN "Danish"
    WHEN REGEXP_MATCH( Language, '^de.*') THEN "German"
    WHEN REGEXP_MATCH( Language, '^dv.*') THEN "Divehi; Dhivehi; Maldivian"
    WHEN REGEXP_MATCH( Language, '^dz.*') THEN "Dzongkha"
    WHEN REGEXP_MATCH( Language, '^ee.*') THEN "Ewe"
    WHEN REGEXP_MATCH( Language, '^el.*') THEN "Greek, Modern (1453-)"
    WHEN REGEXP_MATCH( Language, '^en.*') THEN "English"
    WHEN REGEXP_MATCH( Language, '^eo.*') THEN "Esperanto"
    WHEN REGEXP_MATCH( Language, '^es.*') THEN "Spanish; Castilian"
    WHEN REGEXP_MATCH( Language, '^et.*') THEN "Estonian"
    WHEN REGEXP_MATCH( Language, '^eu.*') THEN "Basque"
    WHEN REGEXP_MATCH( Language, '^fa.*') THEN "Persian"
    WHEN REGEXP_MATCH( Language, '^ff.*') THEN "Fulah"
    WHEN REGEXP_MATCH( Language, '^fi.*') THEN "Finnish"
    WHEN REGEXP_MATCH( Language, '^fj.*') THEN "Fijian"
    WHEN REGEXP_MATCH( Language, '^fo.*') THEN "Faroese"
    WHEN REGEXP_MATCH( Language, '^fr.*') THEN "French"
    WHEN REGEXP_MATCH( Language, '^fy.*') THEN "Western Frisian"
    WHEN REGEXP_MATCH( Language, '^ga.*') THEN "Irish"
    WHEN REGEXP_MATCH( Language, '^gd.*') THEN "Gaelic; Scottish Gaelic"
    WHEN REGEXP_MATCH( Language, '^gl.*') THEN "Galician"
    WHEN REGEXP_MATCH( Language, '^gn.*') THEN "Guarani"
    WHEN REGEXP_MATCH( Language, '^gu.*') THEN "Gujarati"
    WHEN REGEXP_MATCH( Language, '^gv.*') THEN "Manx"
    WHEN REGEXP_MATCH( Language, '^ha.*') THEN "Hausa"
    WHEN REGEXP_MATCH( Language, '^he.*') THEN "Hebrew"
    WHEN REGEXP_MATCH( Language, '^hi.*') THEN "Hindi"
    WHEN REGEXP_MATCH( Language, '^ho.*') THEN "Hiri Motu"
    WHEN REGEXP_MATCH( Language, '^hr.*') THEN "Croatian"
    WHEN REGEXP_MATCH( Language, '^ht.*') THEN "Haitian; Haitian Creole"
    WHEN REGEXP_MATCH( Language, '^hu.*') THEN "Hungarian"
    WHEN REGEXP_MATCH( Language, '^hy.*') THEN "Armenian"
    WHEN REGEXP_MATCH( Language, '^hz.*') THEN "Herero"
    WHEN REGEXP_MATCH( Language, '^ia.*') THEN "Interlingua (International Auxiliary Language Association)"
    WHEN REGEXP_MATCH( Language, '^id.*') THEN "Indonesian"
    WHEN REGEXP_MATCH( Language, '^ie.*') THEN "Interlingue; Occidental"
    WHEN REGEXP_MATCH( Language, '^ig.*') THEN "Igbo"
    WHEN REGEXP_MATCH( Language, '^ii.*') THEN "Sichuan Yi; Nuosu"
    WHEN REGEXP_MATCH( Language, '^ik.*') THEN "Inupiaq"
    WHEN REGEXP_MATCH( Language, '^io.*') THEN "Ido"
    WHEN REGEXP_MATCH( Language, '^is.*') THEN "Icelandic"
    WHEN REGEXP_MATCH( Language, '^it.*') THEN "Italian"
    WHEN REGEXP_MATCH( Language, '^iu.*') THEN "Inuktitut"
    WHEN REGEXP_MATCH( Language, '^ja.*') THEN "Japanese"
    WHEN REGEXP_MATCH( Language, '^jv.*') THEN "Javanese"
    WHEN REGEXP_MATCH( Language, '^ka.*') THEN "Georgian"
    WHEN REGEXP_MATCH( Language, '^kg.*') THEN "Kongo"
    WHEN REGEXP_MATCH( Language, '^ki.*') THEN "Kikuyu; Gikuyu"
    WHEN REGEXP_MATCH( Language, '^kj.*') THEN "Kuanyama; Kwanyama"
    WHEN REGEXP_MATCH( Language, '^kk.*') THEN "Kazakh"
    WHEN REGEXP_MATCH( Language, '^kl.*') THEN "Kalaallisut; Greenlandic"
    WHEN REGEXP_MATCH( Language, '^km.*') THEN "Central Khmer"
    WHEN REGEXP_MATCH( Language, '^kn.*') THEN "Kannada"
    WHEN REGEXP_MATCH( Language, '^ko.*') THEN "Korean"
    WHEN REGEXP_MATCH( Language, '^kr.*') THEN "Kanuri"
    WHEN REGEXP_MATCH( Language, '^ks.*') THEN "Kashmiri"
    WHEN REGEXP_MATCH( Language, '^ku.*') THEN "Kurdish"
    WHEN REGEXP_MATCH( Language, '^kv.*') THEN "Komi"
    WHEN REGEXP_MATCH( Language, '^kw.*') THEN "Cornish"
    WHEN REGEXP_MATCH( Language, '^ky.*') THEN "Kirghiz; Kyrgyz"
    WHEN REGEXP_MATCH( Language, '^la.*') THEN "Latin"
    WHEN REGEXP_MATCH( Language, '^lb.*') THEN "Luxembourgish; Letzeburgesch"
    WHEN REGEXP_MATCH( Language, '^lg.*') THEN "Ganda"
    WHEN REGEXP_MATCH( Language, '^li.*') THEN "Limburgan; Limburger; Limburgish"
    WHEN REGEXP_MATCH( Language, '^ln.*') THEN "Lingala"
    WHEN REGEXP_MATCH( Language, '^lo.*') THEN "Lao"
    WHEN REGEXP_MATCH( Language, '^lt.*') THEN "Lithuanian"
    WHEN REGEXP_MATCH( Language, '^lu.*') THEN "Luba-Katanga"
    WHEN REGEXP_MATCH( Language, '^lv.*') THEN "Latvian"
    WHEN REGEXP_MATCH( Language, '^mg.*') THEN "Malagasy"
    WHEN REGEXP_MATCH( Language, '^mh.*') THEN "Marshallese"
    WHEN REGEXP_MATCH( Language, '^mi.*') THEN "Maori"
    WHEN REGEXP_MATCH( Language, '^mk.*') THEN "Macedonian"
    WHEN REGEXP_MATCH( Language, '^ml.*') THEN "Malayalam"
    WHEN REGEXP_MATCH( Language, '^mn.*') THEN "Mongolian"
    WHEN REGEXP_MATCH( Language, '^mr.*') THEN "Marathi"
    WHEN REGEXP_MATCH( Language, '^ms.*') THEN "Malay"
    WHEN REGEXP_MATCH( Language, '^mt.*') THEN "Maltese"
    WHEN REGEXP_MATCH( Language, '^my.*') THEN "Burmese"
    WHEN REGEXP_MATCH( Language, '^na.*') THEN "Nauru"
    WHEN REGEXP_MATCH( Language, '^nb.*') THEN "Bokmål, Norwegian; Norwegian Bokmål"
    WHEN REGEXP_MATCH( Language, '^nd.*') THEN "Ndebele, North; North Ndebele"
    WHEN REGEXP_MATCH( Language, '^ne.*') THEN "Nepali"
    WHEN REGEXP_MATCH( Language, '^ng.*') THEN "Ndonga"
    WHEN REGEXP_MATCH( Language, '^nl.*') THEN "Dutch; Flemish"
    WHEN REGEXP_MATCH( Language, '^nn.*') THEN "Norwegian Nynorsk; Nynorsk, Norwegian"
    WHEN REGEXP_MATCH( Language, '^no.*') THEN "Norwegian"
    WHEN REGEXP_MATCH( Language, '^nr.*') THEN "Ndebele, South; South Ndebele"
    WHEN REGEXP_MATCH( Language, '^nv.*') THEN "Navajo; Navaho"
    WHEN REGEXP_MATCH( Language, '^ny.*') THEN "Chichewa; Chewa; Nyanja"
    WHEN REGEXP_MATCH( Language, '^oc.*') THEN "Occitan (post 1500)"
    WHEN REGEXP_MATCH( Language, '^oj.*') THEN "Ojibwa"
    WHEN REGEXP_MATCH( Language, '^om.*') THEN "Oromo"
    WHEN REGEXP_MATCH( Language, '^or.*') THEN "Oriya"
    WHEN REGEXP_MATCH( Language, '^os.*') THEN "Ossetian; Ossetic"
    WHEN REGEXP_MATCH( Language, '^pa.*') THEN "Panjabi; Punjabi"
    WHEN REGEXP_MATCH( Language, '^pi.*') THEN "Pali"
    WHEN REGEXP_MATCH( Language, '^pl.*') THEN "Polish"
    WHEN REGEXP_MATCH( Language, '^ps.*') THEN "Pushto; Pashto"
    WHEN REGEXP_MATCH( Language, '^pt.*') THEN "Portuguese"
    WHEN REGEXP_MATCH( Language, '^qu.*') THEN "Quechua"
    WHEN REGEXP_MATCH( Language, '^rm.*') THEN "Romansh"
    WHEN REGEXP_MATCH( Language, '^rn.*') THEN "Rundi"
    WHEN REGEXP_MATCH( Language, '^ro.*') THEN "Romanian; Moldavian; Moldovan"
    WHEN REGEXP_MATCH( Language, '^ru.*') THEN "Russian"
    WHEN REGEXP_MATCH( Language, '^rw.*') THEN "Kinyarwanda"
    WHEN REGEXP_MATCH( Language, '^sa.*') THEN "Sanskrit"
    WHEN REGEXP_MATCH( Language, '^sc.*') THEN "Sardinian"
    WHEN REGEXP_MATCH( Language, '^sd.*') THEN "Sindhi"
    WHEN REGEXP_MATCH( Language, '^se.*') THEN "Northern Sami"
    WHEN REGEXP_MATCH( Language, '^sg.*') THEN "Sango"
    WHEN REGEXP_MATCH( Language, '^si.*') THEN "Sinhala; Sinhalese"
    WHEN REGEXP_MATCH( Language, '^sk.*') THEN "Slovak"
    WHEN REGEXP_MATCH( Language, '^sl.*') THEN "Slovenian"
    WHEN REGEXP_MATCH( Language, '^sm.*') THEN "Samoan"
    WHEN REGEXP_MATCH( Language, '^sn.*') THEN "Shona"
    WHEN REGEXP_MATCH( Language, '^so.*') THEN "Somali"
    WHEN REGEXP_MATCH( Language, '^sq.*') THEN "Albanian"
    WHEN REGEXP_MATCH( Language, '^sr.*') THEN "Serbian"
    WHEN REGEXP_MATCH( Language, '^ss.*') THEN "Swati"
    WHEN REGEXP_MATCH( Language, '^st.*') THEN "Sotho, Southern"
    WHEN REGEXP_MATCH( Language, '^su.*') THEN "Sundanese"
    WHEN REGEXP_MATCH( Language, '^sv.*') THEN "Swedish"
    WHEN REGEXP_MATCH( Language, '^sw.*') THEN "Swahili"
    WHEN REGEXP_MATCH( Language, '^ta.*') THEN "Tamil"
    WHEN REGEXP_MATCH( Language, '^te.*') THEN "Telugu"
    WHEN REGEXP_MATCH( Language, '^tg.*') THEN "Tajik"
    WHEN REGEXP_MATCH( Language, '^th.*') THEN "Thai"
    WHEN REGEXP_MATCH( Language, '^ti.*') THEN "Tigrinya"
    WHEN REGEXP_MATCH( Language, '^tk.*') THEN "Turkmen"
    WHEN REGEXP_MATCH( Language, '^tl.*') THEN "Tagalog"
    WHEN REGEXP_MATCH( Language, '^tn.*') THEN "Tswana"
    WHEN REGEXP_MATCH( Language, '^to.*') THEN "Tonga (Tonga Islands)"
    WHEN REGEXP_MATCH( Language, '^tr.*') THEN "Turkish"
    WHEN REGEXP_MATCH( Language, '^ts.*') THEN "Tsonga"
    WHEN REGEXP_MATCH( Language, '^tt.*') THEN "Tatar"
    WHEN REGEXP_MATCH( Language, '^tw.*') THEN "Twi"
    WHEN REGEXP_MATCH( Language, '^ty.*') THEN "Tahitian"
    WHEN REGEXP_MATCH( Language, '^ug.*') THEN "Uighur; Uyghur"
    WHEN REGEXP_MATCH( Language, '^uk.*') THEN "Ukrainian"
    WHEN REGEXP_MATCH( Language, '^ur.*') THEN "Urdu"
    WHEN REGEXP_MATCH( Language, '^uz.*') THEN "Uzbek"
    WHEN REGEXP_MATCH( Language, '^ve.*') THEN "Venda"
    WHEN REGEXP_MATCH( Language, '^vi.*') THEN "Vietnamese"
    WHEN REGEXP_MATCH( Language, '^vo.*') THEN "Volapük"
    WHEN REGEXP_MATCH( Language, '^wa.*') THEN "Walloon"
    WHEN REGEXP_MATCH( Language, '^wo.*') THEN "Wolof"
    WHEN REGEXP_MATCH( Language, '^xh.*') THEN "Xhosa"
    WHEN REGEXP_MATCH( Language, '^yi.*') THEN "Yiddish"
    WHEN REGEXP_MATCH( Language, '^yo.*') THEN "Yoruba"
    WHEN REGEXP_MATCH( Language, '^za.*') THEN "Zhuang; Chuang"
    WHEN REGEXP_MATCH( Language, '^zh.*') THEN "Chinese"
    WHEN REGEXP_MATCH( Language, '^zu.*') THEN "Zulu"
    ELSE "Other"
    END

Looker Studio extract external link clicks to local authorites

REGEXP_CONTAINS(click_url,'aberdeencity.gov.uk|aberdeenshire.gov.uk|angus.gov.uk|argyll-bute.gov.uk|edinburgh.gov.uk|clacks.gov.uk|cne-siar.gov.uk|dumgal.gov.uk|dundeecity.gov.uk|east-ayrshire.gov.uk|eastdunbarton.gov.uk|eastlothian.gov.uk|eastrenfrewshire.gov.uk|falkirk.gov.uk|fife.gov.uk|glasgow.gov.uk|inverclyde.gov.uk|midlothian.gov.uk|north-ayrshire.gov.uk|northlanarkshire.gov.uk|orkney.gov.uk|pkc.gov.uk|renfrewshire.gov.uk|scotborders.gov.uk|shetland.gov.uk|south-ayrshire.gov.uk|southlanarkshire.gov.uk|stirling.gov.uk|highland.gov.uk|moray.gov.uk|west-dunbarton.gov.uk|westlothian.gov.uk')