function organizeKeywordDataFromAllSheets() {
var sheet = [Link]();
var allSheets = [Link]();
[Link](sourceSheet => {
var sheetName = [Link]();
if ([Link]("KW Mapping")) return; // Avoid overwriting formatted
sheets
var targetSheetName = "KW Mapping - " + sheetName; // Follow the naming
pattern
var targetSheet = [Link](targetSheetName) ||
[Link](targetSheetName);
[Link](); // Clear previous data
var data = [Link]().getValues();
var hyperlinks = extractHyperlinks(sourceSheet); // Extract subcategory URLs
if ([Link] < 2) return; // Skip empty sheets
var outputRow = 1;
var processedSubcategories = {};
for (var i = 1; i < [Link]; i++) {
var subcategory = data[i][1]; // Sub-category
var keyword = data[i][2]; // Keyword
var searchVolume = data[i][3]; // Search Volume
var avgPosition = ""; // Placeholder if Avg Position is needed
var intent = data[i][4]; // Intent
var subcategoryUrl = hyperlinks[i][1] || "#"; // Extracted subcategory URL
if (!processedSubcategories[subcategory]) {
// Leave two empty rows before a new subcategory
outputRow += 2;
// Insert Page Status Header
[Link](outputRow, 1).setValue("Page Status");
[Link](outputRow, 2).setValue("Existing");
[Link](outputRow, 3).setValue(subcategoryUrl); // Paste
actual URL
// Apply formatting for Page Status row
formatHeaderRow(targetSheet, outputRow);
outputRow++;
// Insert Column Headers as per example template
[Link](outputRow, 1, 1, 4).setValues([
["Keyword", "Avg. monthly searches", "Avg Position", "Intent"]
]);
formatColumnHeaders(targetSheet, outputRow);
outputRow++;
processedSubcategories[subcategory] = true;
}
// Insert Keyword Data
[Link](outputRow, 1, 1, 4).setValues([[keyword, searchVolume,
avgPosition, intent]]);
outputRow++;
}
// Apply final border formatting
applyBorderStyling(targetSheet, outputRow);
});
[Link]().alert("Data has been successfully formatted and
structured!");
}
// Function to extract hyperlinks from the subcategory column
function extractHyperlinks(sheet) {
var range = [Link]();
var richTextValues = [Link]();
var urls = [];
for (var i = 0; i < [Link]; i++) {
var rowUrls = [];
for (var j = 0; j < richTextValues[i].length; j++) {
var richText = richTextValues[i][j];
if (richText) {
var url = [Link]();
[Link](url ? url : ""); // Store URL or empty string
} else {
[Link]("");
}
}
[Link](rowUrls);
}
return urls;
}
// Function to format the Page Status row
function formatHeaderRow(sheet, row) {
var range = [Link](row, 1, 1, 3);
[Link]("#B7DEE8"); // Light blue
[Link]("bold");
[Link]("left");
}
// Function to format column headers (Keyword, Search Volume, Avg Position, Intent)
function formatColumnHeaders(sheet, row) {
var range = [Link](row, 1, 1, 4);
[Link]("#F4B084"); // Light orange
[Link]("bold");
[Link]("center");
}
// Function to apply black borders around structured sections & white borders
elsewhere
function applyBorderStyling(sheet, lastRow) {
var range = [Link]();
var allCells = [Link]();
for (var r = 0; r < [Link]; r++) {
for (var c = 0; c < allCells[r].length; c++) {
if (allCells[r][c] !== "") {
// Apply black border for populated cells (structured table)
[Link](r + 1, c + 1).setBorder(true, true, true, true, true, true,
"black");
} else {
// Apply white (invisible) border for empty spaces
[Link](r + 1, c + 1).setBorder(false, false, false, false, false, false,
"white");
}
}
}
}