Proje numarasına gidin, seçin ve CTRL + C kullanarak kopyalayın.
Editörün içinde appsscript.json dosyasına gidin ve dosyadaki her şeyi aşağıdaki kodla değiştirin:
const scriptProperties = PropertiesService.getScriptProperties();
const OPENAI_URL = “https://api.openai.com/v1/chat/completions”;
const SYSTEM_MESSAGE = { role: “system”, content: “You are a helpful SEO expert.” };
function log(message) {
Logger.log(message); // Regular Apps Script logging
constlogs = scriptProperties.getProperty(‘customLogs’) || ”;
scriptProperties.setProperty(‘customLogs’, logs + ‘\n’ + message); // Append message to logs
}
function resetLogs() {
scriptProperties.deleteProperty(‘customLogs’); // Clear logs for a new execution
}
function getLogs() {
returnscriptProperties.getProperty(‘customLogs’) || ‘No logs available.’;
}
function fetchOAuthToken() {
lettoken = scriptProperties.getProperty(‘oauthToken’);
if (!token) {
token = ScriptApp.getOAuthToken();
scriptProperties.setProperty(‘oauthToken’, token);
log(‘OAuth token fetched and stored.’);
}
returntoken;
}
function onOpen() {
constui = SpreadsheetApp.getUi();
ui.createMenu(‘Search Console’)
.addItem(‘Authorize GSC’, ‘promptReauthorization’)
.addItem(‘Fetch GSC Properties’, ‘fetchGSCProperties’)
.addItem(‘Select URL’, ‘fetchUrlsForSelectedProperty’)
.addItem(‘Fetch Keywords’, ‘fetchKeywordsForSelectedUrl’) // Yeni menü ekleme
.addItem(‘Inspect URL’, ‘inspectUrl’)
.addItem(‘AI Analyze’, ‘aiAnalyze’)
.addToUi();
}
function fetchKeywordsForSelectedUrl() {
constsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
constselectedProperty = sheet.getRange(‘B1’).getValue();
constselectedUrl = sheet.getRange(‘B2’).getValue();
if (!selectedProperty || selectedProperty === ‘Select a property’ || !selectedUrl || selectedUrl === ‘Select a URL’) {
SpreadsheetApp.getUi().alert(‘Please select a valid property and URL before fetching keywords.’);
return;
}
constoauthToken = fetchOAuthToken();
constkeywords = getTopKeywordsForUrl(selectedProperty, selectedUrl, oauthToken);
if (!keywords || keywords.length === 0) {
sheet.getRange(‘A3’).setValue(‘No keywords found’).setFontWeight(‘bold’);
return;
}
sheet.getRange(‘A3:D12’).clearContent();
sheet.getRange(‘A3’).setValue(‘Keyword’).setFontWeight(‘bold’);
sheet.getRange(‘B3’).setValue(‘Clicks’).setFontWeight(‘bold’);
sheet.getRange(‘C3’).setValue(‘Impressions’).setFontWeight(‘bold’);
sheet.getRange(‘D3’).setValue(‘CTR’).setFontWeight(‘bold’);
keywords.forEach((keyword, index) => {
if (index < 10) {
sheet.getRange(`A${3 + index}`).setValue(keyword.query).setFontWeight(‘bold’);
sheet.getRange(`B${3 + index}`).setValue(keyword.clicks);
sheet.getRange(`C${3 + index}`).setValue(keyword.impressions);
sheet.getRange(`D${3 + index}`).setValue(keyword.ctr);
}
});
}
function fetchUrlsForSelectedProperty() {
constsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
constselectedProperty = sheet.getRange(‘B1’).getValue();
if (!selectedProperty || selectedProperty === ‘Select a property’) {
SpreadsheetApp.getUi().alert(‘Please select a valid property before fetching URLs.’);
return;
}
constoauthToken = fetchOAuthToken();
consturls = getUrlsForProperty(selectedProperty, oauthToken);
if (!urls || urls.length === 0) {
sheet.getRange(‘A2’).setValue(‘No URLs found’).setFontWeight(‘bold’);
sheet.getRange(‘B2’).clearContent();
log(`No URLs found for property ${selectedProperty}`);
return;
}
sheet.getRange(‘A2’).setValue(‘Select a URL’).setFontWeight(‘bold’);
sheet.getRange(‘B2’).setDataValidation(
SpreadsheetApp.newDataValidation()
.requireValueInList([‘Select a URL’].concat(urls), true)
.build()
);
sheet.getRange(‘B2’).setValue(‘Select a URL’).setFontWeight(‘bold’);
}
function promptReauthorization() {
constui = SpreadsheetApp.getUi();
constresponse = ui.alert(
‘Re-authorize Script’,
‘Re-authorizing will revoke current permissions and require you to authorize again. Do you want to continue?’,
ui.ButtonSet.YES_NO
);
if (response === ui.Button.YES) {
try {
scriptProperties.deleteProperty(‘oauthToken’); // Clear old token
consttoken = fetchOAuthToken(); // Fetch and store new token
log(“OAuth Token: ” + token);
ui.alert(‘Authorization successful. No further action is required.’);
} catch (e) {
ui.alert(‘Authorization failed: ‘ + e.toString());
}
} else {
ui.alert(‘Re-authorization canceled.’);
}
}
function fetchGSCProperties() {
resetLogs();
constsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
constoauthToken = fetchOAuthToken();
constsites = getSitesListFromGSC(oauthToken);
if (!sites || sites.length === 0) {
SpreadsheetApp.getUi().alert(‘No GSC properties found. Please ensure you have access to GSC properties.’);
return;
}
constsiteUrls = [‘Select a property’].concat(
sites.map(site => site.siteUrl).sort()
);
sheet.getRange(‘A1’).setValue(‘Select GSC property’).setFontWeight(‘bold’);
sheet.getRange(‘B1’).setDataValidation(
SpreadsheetApp.newDataValidation()
.requireValueInList(siteUrls, true)
.build()
);
sheet.getRange(‘B1’).setValue(‘Select a property’).setFontWeight(‘bold’);
sheet.setColumnWidth(1, 150);
sheet.setColumnWidth(2, 350);
}
let isProcessing = false; // Global flag to prevent recursive triggering
function getApiRequestDetails(selectedProperty) {
constpayload = {
startDate: getThreeMonthsAgo(),
endDate: getToday(),
dimensions: [“page”],
rowLimit: 100,
orderBy: [{ fieldName: “clicks”, sortOrder: “DESCENDING” }]
};
constapiUrl = `https://www.googleapis.com/webmasters/v3/sites/${encodeURIComponent(selectedProperty)}/searchAnalytics/query`;
return { url: apiUrl, payload: payload };
}
function getSitesListFromGSC(oauthToken) {
try {
consturl = ‘https://www.googleapis.com/webmasters/v3/sites’;
constheaders = {
‘Authorization’: ‘Bearer ‘ + oauthToken,
‘Content-Type’: ‘application/json’
};
constoptions = {
method: ‘get’,
headers: headers,
muteHttpExceptions: true
};
constresponse = UrlFetchApp.fetch(url, options);
log(`Response Code: ${response.getResponseCode()}`);
log(`Response Content: ${response.getContentText()}`);
if (response.getResponseCode() === 200) {
constjson = JSON.parse(response.getContentText());
returnjson.siteEntry || [];
} else {
thrownewError(`Error fetching data: ${response.getResponseCode()} – ${response.getContentText()}`);
}
} catch (e) {
log(`Error: ${e.toString()}`);
return [];
}
}
function getUrlsForProperty(property, oauthToken) {
try {
constapiUrl = `https://www.googleapis.com/webmasters/v3/sites/${encodeURIComponent(property)}/searchAnalytics/query`;
log(`API URL: ${apiUrl}`);
log(`OAuth Token: ${oauthToken}`);
constpayload = {
startDate: getThreeMonthsAgo(),
endDate: getToday(),
dimensions: [“page”],
rowLimit: 100,
orderBy: [{ fieldName: “clicks”, sortOrder: “DESCENDING” }]
};
log(`Payload: ${JSON.stringify(payload)}`);
constheaders = {
Authorization: `Bearer ${oauthToken}`,
“Content-Type”: “application/json”
};
constoptions = {
method: “post”,
contentType: “application/json”,
headers: headers,
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
constresponse = UrlFetchApp.fetch(apiUrl, options);
log(`Response Code: ${response.getResponseCode()}`);
log(`Response: ${response.getContentText()}`);
if (response.getResponseCode() === 200) {
constjson = JSON.parse(response.getContentText());
returnjson.rows ? json.rows.map(row => row.keys[0]) : [];
} else {
thrownewError(`Failed to fetch data: ${response.getResponseCode()} – ${response.getContentText()}`);
}
} catch (e) {
log(`Error: ${e.toString()}`);
return [];
}
}
function getTopKeywordsForUrl(property, url, oauthToken) {
try {
constapiUrl = `https://www.googleapis.com/webmasters/v3/sites/${encodeURIComponent(property)}/searchAnalytics/query`;
log(`API URL: ${apiUrl}`);
log(`OAuth Token: ${oauthToken}`);
constpayload = {
startDate: getThreeMonthsAgo(),
endDate: getToday(),
dimensions: [“query”],
dimensionFilterGroups: [
{
filters: [
{
dimension: “page”,
operator: “equals”,
expression: url
}
]
}
],
rowLimit: 10,
orderBy: [{ fieldName: “clicks”, sortOrder: “DESCENDING” }]
};
log(`Payload: ${JSON.stringify(payload)}`);
constheaders = {
Authorization: `Bearer ${oauthToken}`,
“Content-Type”: “application/json”
};
constoptions = {
method: “post”,
contentType: “application/json”,
headers: headers,
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
constresponse = UrlFetchApp.fetch(apiUrl, options);
log(`Response Code: ${response.getResponseCode()}`);
log(`Response: ${response.getContentText()}`);
if (response.getResponseCode() === 200) {
constjson = JSON.parse(response.getContentText());
returnjson.rows ? json.rows.map(row => ({
query: row.keys[0],
clicks: row.clicks,
impressions: row.impressions,
ctr: row.ctr
})) : [];
} else {
thrownewError(`Failed to fetch data: ${response.getResponseCode()} – ${response.getContentText()}`);
}
} catch (e) {
log(`Error: ${e.toString()}`);
return [];
}
}
function getToday() {
consttoday = newDate();
returntoday.toISOString().split(“T”)[0];
}
function getThreeMonthsAgo() {
constdate = newDate();
date.setMonth(date.getMonth() – 3);
returndate.toISOString().split(“T”)[0];
}
function inspectUrl() {
constsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
consturl = sheet.getRange(‘B2’).getValue();
constproperty = sheet.getRange(‘B1’).getValue();
// Clear previous inspection results in A15:D30
sheet.getRange(‘A15:D30’).clearContent();
sheet.getRange(‘A15’).setValue(‘Inspecting…’).setFontWeight(‘bold’);
if (!url || url === ‘Select a URL’) {
SpreadsheetApp.getUi().alert(‘Please select a valid URL in cell B2 before inspecting.’);
sheet.getRange(‘A15’).setValue(‘No URL selected’).setFontWeight(‘bold’);
return;
}
constoauthToken = fetchOAuthToken();
try {
constresult = callUrlInspectionApi(property, url, oauthToken);
// Extract fields from the response
constindexStatus = result.indexStatusResult || {};
constmobileUsability = result.mobileUsabilityResult || {};
constrichResults = result.richResultsInfo || {};
constreferringUrls = indexStatus.referringUrls?.join(‘, ‘) || ‘None’;
// Populate inspection results in the sheet
sheet.getRange(‘A15’).setValue(`Inspection Results`).setFontWeight(‘bold’);
sheet.getRange(‘A16’).setValue(`URL:`).setFontWeight(‘bold’);
sheet.getRange(‘B16’).setValue(url);
sheet.getRange(‘A17’).setValue(`Coverage:`).setFontWeight(‘bold’);
sheet.getRange(‘B17’).setValue(indexStatus.coverageState || ‘Unknown’);
sheet.getRange(‘A18’).setValue(`Robots.txt:`).setFontWeight(‘bold’);
sheet.getRange(‘B18’).setValue(indexStatus.robotsTxtState || ‘Unknown’);
sheet.getRange(‘A19’).setValue(`Indexing State:`).setFontWeight(‘bold’);
sheet.getRange(‘B19’).setValue(indexStatus.indexingState || ‘Unknown’);
sheet.getRange(‘A20’).setValue(`Last Crawled:`).setFontWeight(‘bold’);
sheet.getRange(‘B20’).setValue(indexStatus.lastCrawlTime || ‘Not Available’);
sheet.getRange(‘A21’).setValue(`Google Canonical:`).setFontWeight(‘bold’);
sheet.getRange(‘B21’).setValue(indexStatus.googleCanonical || ‘Unknown’);
sheet.getRange(‘A22’).setValue(`User Canonical:`).setFontWeight(‘bold’);
sheet.getRange(‘B22’).setValue(indexStatus.userCanonical || ‘Unknown’);
sheet.getRange(‘A23’).setValue(`Mobile Usability:`).setFontWeight(‘bold’);
sheet.getRange(‘B23’).setValue(mobileUsability.verdict || ‘Unknown’);
sheet.getRange(‘A24’).setValue(`Rich Results Eligibility:`).setFontWeight(‘bold’);
sheet.getRange(‘B24’).setValue(richResults.verdict || ‘Unknown’);
sheet.getRange(‘A25’).setValue(`Referring URLs:`).setFontWeight(‘bold’);
sheet.getRange(‘B25’).setValue(referringUrls);
// Log and alert full response for debugging
constfullResponse = JSON.stringify(result, null, 2);
log(`Full Inspection Result: ${fullResponse}`);
//SpreadsheetApp.getUi().alert(`Inspection Completed. Full Response:\n\n${fullResponse}`);
} catch (error) {
sheet.getRange(‘A15’).setValue(‘Inspection Failed’).setFontWeight(‘bold’);
log(`Error inspecting URL: ${error.message}`);
SpreadsheetApp.getUi().alert(`Error inspecting URL: ${error.message}\n\nLogs:\n${getLogs()}`);
}
}
function callUrlInspectionApi(property, url, oauthToken) {
constapiUrl = ‘https://searchconsole.googleapis.com/v1/urlInspection/index:inspect’;
constpayload = {
siteUrl: property,
inspectionUrl: url,
languageCode: ‘en-US’
};
constheaders = {
Authorization: `Bearer ${oauthToken}`,
‘Content-Type’: ‘application/json’
};
constoptions = {
method: ‘post’,
contentType: ‘application/json’,
headers: headers,
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
log(`API URL: ${apiUrl}`);
log(`Payload: ${JSON.stringify(payload)}`);
try {
constresponse = UrlFetchApp.fetch(apiUrl, options);
constresponseCode = response.getResponseCode();
constresponseText = response.getContentText();
log(`Response Code: ${responseCode}`);
log(`Response Content: ${responseText}`);
if (responseCode === 200) {
constjsonResponse = JSON.parse(responseText);
if (jsonResponse && jsonResponse.inspectionResult) {
returnjsonResponse.inspectionResult;
} else {
log(`Unexpected API Response Structure: ${responseText}`);
thrownewError(‘Unexpected API response format. “inspectionResult” field is missing.’);
}
} else {
log(`Failed API Call: ${responseText}`);
thrownewError(`Failed to inspect URL. Response Code: ${responseCode}. Response: ${responseText}`);
}
} catch (error) {
log(`Error during API call: ${error}`);
thrownewError(`Error inspecting URL: ${error.message}`);
}
}
function callChatGPT(prompt, temperature = 0.9, maxTokens = 800, model = “gpt-3.5-turbo”) {
constsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
constsecretKey = sheet.getRange(‘D1’).getValue().trim(); // Retrieve the OpenAI API key from D1
if (!secretKey) {
thrownewError(“API Key is missing in cell D1. Please provide a valid OpenAI API key.”);
}
constpayload = {
model: model,
messages: [
SYSTEM_MESSAGE,
{ role: “user”, content: prompt }
],
temperature: temperature,
max_tokens: maxTokens
};
constoptions = {
method: “POST”,
headers: {
“Content-Type”: “application/json”,
“Authorization”: “Bearer ” + secretKey
},
payload: JSON.stringify(payload)
};
try {
constresponse = UrlFetchApp.fetch(OPENAI_URL, options);
constresponseData = JSON.parse(response.getContentText());
if (responseData.choices && responseData.choices[0] && responseData.choices[0].message) {
returnresponseData.choices[0].message.content.trim();
} else {
log(“Unexpected response format from OpenAI: ” + JSON.stringify(responseData));
return”Sorry, I couldn’t process the request.”;
}
} catch (error) {
log(“Error calling OpenAI API: ” + error);
return”Sorry, there was an error processing your request.”;
}
}
function aiAnalyze() {
constsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
consturl = sheet.getRange(‘B2’).getValue();
constkeywords = sheet.getRange(‘A3:A12’).getValues().flat().filter(Boolean); // Get non-empty keywords
constinspectionData = sheet.getRange(‘A16:B20’).getValues();
// Validate input fields
if (!url || keywords.length === 0 || inspectionData.some(row => row.length < 2 || !row[0].trim() || !row[1].trim())) {
SpreadsheetApp.getUi().alert(“Ensure the following are filled before running AI Analyze:\n- URL in B2\n- Keywords in A3:A12\n- Inspection data in A16:B20”);
return;
}
// Prepare the prompt for ChatGPT
constprompt = `
Analyze this URL: ${url}
Also the view-source version from: ${url}
against these keywords: ${keywords.join(“, “)}
Considering the URL inspection data from Google Search Console:
${inspectionData.map(row => `${row[0]}: ${row[1]}`).join(“\n”)}
Suggest a short list of specific recommendations on how I can improve the page’s SEO. Make sure the recommendations include details such as change this to that, or add something, etc… Be concrete with SEO recommendations.
`;
// Display the prompt in G1
sheet.getRange(‘G1’).setValue(“Prompt Sent to ChatGPT”).setFontWeight(“bold”);
sheet.getRange(‘G2:G30’).clearContent(); // Clear previous content in column G
sheet.getRange(‘G2:G30’).merge(); // Merge cells G2:G30
sheet.getRange(‘G2’).setValue(prompt).setVerticalAlignment(“top”); // Add the prompt and align to top
sheet.setColumnWidth(7, 400); // Set column G width to 400px
// Call ChatGPT API
constanalysisResult = callChatGPT(prompt);
// Display the result in the spreadsheet (Column F)
sheet.getRange(‘F1’).setValue(“AI Analysis Result”).setFontWeight(“bold”);
sheet.getRange(‘F2:F30’).clearContent(); // Clear previous content
sheet.getRange(‘F2:F30’).merge(); // Merge the cells
sheet.getRange(‘F2’).setValue(analysisResult).setVerticalAlignment(“top”); // Add the AI result and align to top
sheet.setColumnWidth(6, 400); // Set column F width to 400px
// Log the response
log(“AI Analysis Completed: ” + analysisResult);
}
