function onOpen() {
    SpreadsheetApp.getUi()
        .createMenu("Re-directs")
        .addItem('Final re-directs', 'run')
        .addItem('Final cleanup', 'finalCleanup')
        .addToUi();
}

function run() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    ss.toast("Starting execution ...")
    var sheet1 = ss.getSheetByName("1 Analytics");
    var sheet2 = ss.getSheetByName("2 Screaming Frog");
    var sheet3 = ss.getSheetByName("3 Ahrefs backlinks");
    var sheet = ss.getSheetByName("prefinal");
    if(!sheet) sheet = ss.insertSheet("prefinal");
    sheet.getDataRange()
        .clear();
    var v1 = sheet1.getDataRange()
        .getValues();
    sheet.getRange(1, 1, v1.length, v1[0].length)
        .setValues(v1)
    var v2 = sheet2.getRange("A2:A" + sheet2.getLastRow())
        .getValues();
    sheet.getRange(sheet.getLastRow() + 1, 1, v2.length, v2[0].length)
        .setValues(v2)
    var v3 = sheet3.getRange("N2:N" + sheet3.getLastRow())
        .getValues();
    sheet.getRange(sheet.getLastRow() + 1, 1, v3.length, v3[0].length)
        .setValues(v3)

    SpreadsheetApp.flush()

    var v = sheet.getRange("A2:A" + sheet.getLastRow())
        .getValues();
    for(var i = 0; i < v.length; i++) {
        //if(v[i][0].indexOf("http") != 0) {
        v[i][0] = v[i][0]?.replace(/.*\/\/[^\/]*/, '');
        v[i][0] = v[i][0]?.split("?")[0];
        //}
    }

    ss.toast("Please wait ...")
    sheet.getRange("A2:A" + sheet.getLastRow())
        .setValues(v);

    SpreadsheetApp.flush()

    sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns())
        .offset(1, 0, sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns())
            .getNumRows() - 1)
        .sort([{
            column: 2,
            ascending: false
        }, {
            column: 1,
            ascending: true
        }]);
    var values = sheet.getDataRange()
        .getValues();
    var db = {};
    v = []
    for(var i = 0; i < values.length; i++) {
        string = values[i][0]?.split("?")[0];
        values[i][0] = string;
        if(db.hasOwnProperty(string)) {
            ;
        } else {
            v.push(values[i])
        }
        db[string] = "";
    }

    //remove the jpg pdf etc

    v = v.map(row => {
            let fileName = row[0];
            let fileExtension = fileName.split('.')
                .pop();
            if(fileExtension === 'jpg' || fileExtension === 'png' || fileExtension === 'svg' || fileExtension === 'jpeg' || fileExtension === 'pdf') {
                return undefined
            } else {
                return row;
            }
        })
        .filter(Boolean)

    resultSheet = ss.getSheetByName("Final re-directs")
    if(!resultSheet) resultSheet = ss.insertSheet("Final re-directs");
    resultSheet.getDataRange()
        .clearContent()
    resultSheet.getRange(1, 1, v.length, v[0].length)
        .setValues(v);
    ss.deleteSheet(sheet);
    resultSheet.activate();
}

function finalCleanup() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    ss.toast("Starting execution ...")
    var sheet1 = ss.getSheetByName("1 Analytics");
    var sheet2 = ss.getSheetByName("2 Screaming Frog");
    var sheet3 = ss.getSheetByName("3 Ahrefs backlinks");
    var sheet = ss.getSheetByName("Ready for customer");
    if(!sheet) sheet = ss.insertSheet("Ready for customer");
    sheet.clear()
    sheet.getRange("A1")
        .setValue(`=arrayformula(iferror(SPLIT('4 Getredirects'!A1:A,",",true)))`);
    SpreadsheetApp.flush()
    values = sheet.getDataRange()
        .getValues()
    sheet.getRange(1, 1, values.length, values[0].length)
        .setValues(values)
    sheet.getDataRange()
        .removeDuplicates([1, 2]);
    sheet.getRange("D1")
        .setValue(`={"Side";arrayformula(if(A2:A<>"","/"&RIGHT(A2:A, LEN(A2:A)-(FIND("/",A2:A,FIND("/",A2:A,FIND("/",A2:A)+1)+1))),""))}`)
    sheet.getRange("E1")
        .setValue(`={"Sidevisninger";arrayformula(if(A2:A<>"",iferror(VLOOKUP(D2:D,'Final re-directs'!$A:$B,2,false)),""))}
`)
    SpreadsheetApp.flush();
    values = sheet.getDataRange()
        .getValues()
    sheet.getRange(1, 1, values.length, values[0].length)
        .setValues(values)
    sheet.deleteColumns(4, 1);

}