appscript.json:
{
"timeZone": "America/New_York",
"dependencies": {
"enabledAdvancedServices": [
{
"userSymbol": "AdminDirectory",
"version": "directory_v1",
"serviceId": "admin"
},
{
"userSymbol": "AdminReports",
"serviceId": "admin",
"version": "reports_v1"
},
{
"userSymbol": "BigQuery",
"serviceId": "bigquery",
"version": "v2"
},
{
"userSymbol": "Chat",
"version": "v1",
"serviceId": "chat"
},
{
"userSymbol": "People",
"serviceId": "peopleapi",
"version": "v1"
}
]
},
"executionApi": {
"access": "DOMAIN"
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/bigquery",
"https://www.googleapis.com/auth/chat.spaces.readonly",
"https://www.googleapis.com/auth/cloud-billing.readonly",
"https://www.googleapis.com/auth/cloud-platform",
"https://www.googleapis.com/auth/devstorage.read_only",
"https://www.googleapis.com/auth/drive",
"https://www.googleapis.com/auth/ediscovery",
"https://www.googleapis.com/auth/generative-language.retriever",
"https://www.googleapis.com/auth/logging.read",
"https://www.googleapis.com/auth/script.external_request",
"https://www.googleapis.com/auth/spreadsheets"
],
"chat": {}
}
function hardResetAuth() {
ScriptApp.invalidateAuth();
}
const GEMINI_API_KEY = PropertiesService.getScriptProperties().getProperty("GEMINI_API_KEY");
// --- API Key Masking Function ---
/**
* Masks API keys found in a string.
* Looks for patterns like "?key=XXX" or "&key=XXX" and replaces the key value.
* @param {string} text The string potentially containing an API key.
* @return {string} The string with the API key masked.
*/
function maskApiKey(text) {
if (typeof text !== "string") {
return text; // Return non-strings as is
}
// This regex looks for "?key=" or "&key=" followed by any characters
// that are NOT "&" or space (to avoid consuming subsequent parameters or parts of message)
// It replaces the key part with "REDACTED".
// The ($1) captures the preceding "?" or "&" and puts it back.
return text.replace(/([?&])key=[^&\s]+/, "$1key=REDACTED");
}
// --- Automatic Log Masking Setup ---
const originalConsoleLog = console.log;
// Redefine console.log to automatically mask API keys in string arguments
console.log = function (/** @type {any[]} */ ...args) {
const maskedArgs = args.map((arg) => maskApiKey(arg));
originalConsoleLog.apply(console, maskedArgs);
};
const originalConsoleError = console.error;
console.error = function (/** @type {any[]} */ ...args) {
const maskedArgs = args.map((arg) => maskApiKey(arg));
originalConsoleError.apply(console, maskedArgs);
};
const originalConsoleWarn = console.warn;
console.warn = function (/** @type {any[]} */ ...args) {
const maskedArgs = args.map((arg) => maskApiKey(arg));
originalConsoleWarn.apply(console, maskedArgs);
};
const originalConsoleInfo = console.info;
console.info = function (/** @type {any[]} */ ...args) {
const maskedArgs = args.map((arg) => maskApiKey(arg));
originalConsoleInfo.apply(console, maskedArgs);
};
Post Gemini to space
function runWeeklyReport() {
const today = new Date();
const format = (d) => Utilities.formatDate(d, Session.getScriptTimeZone(), 'yyyy-MM-dd');
const startObj = new Date(); startObj.setDate(today.getDate() - 8);
const endObj = new Date(); endObj.setDate(today.getDate() - 1);
const startDate = format(startObj);
const endDate = format(endObj);
const prompt = `
Return three articles and their citations for the latest in TOPIC in the public sector from ${startDate} - ${endDate}?
The articles should be very specific to the federal government, a state government or a city government.
Avoid articles that talk in large terms about the importance of TOPIC.
`;
postGeminiToSpace(prompt);
}
/**
* Posts a Gemini response to a Google Chat Space based on a prompt.
* Requires 'GEMINI_API_KEY' and 'WEBHOOK_URL' in Script Properties.
* * @param {string} prompt - The text prompt to send to Gemini.
*/
function postGeminiToSpace(prompt) {
const props = PropertiesService.getScriptProperties();
const apiKey = props.getProperty('GEMINI_API_KEY');
const webhookUrl = props.getProperty('WEBHOOK_URL');
if (!apiKey || !webhookUrl) {
console.error("Missing Script Properties: Ensure GEMINI_API_KEY and WEBHOOK_URL are set.");
return;
}
const modelId = "gemini-3-flash-preview";
const apiUrl = `https://generativelanguage.googleapis.com/v1beta/models/${modelId}:streamGenerateContent?key=${apiKey}`;
const payload = {
"contents": [{ "role": "user", "parts": [{ "text": prompt }] }],
"generationConfig": { "thinkingConfig": { "thinkingLevel": "HIGH" } },
"tools": [{ "googleSearch": {} }, { "urlContext": {} }]
};
const options = {
"method": "post",
"contentType": "application/json",
"payload": JSON.stringify(payload),
"muteHttpExceptions": true
};
try {
console.log("▶️ Calling Gemini...");
const response = UrlFetchApp.fetch(apiUrl, options);
if (response.getResponseCode() !== 200) {
throw new Error(`Gemini API Error: ${response.getContentText()}`);
}
// The API returns a JSON array of chunks. We must join the text parts.
const jsonArray = JSON.parse(response.getContentText());
let finalOutput = "";
if (Array.isArray(jsonArray)) {
jsonArray.forEach(chunk => {
if (chunk.candidates?.[0]?.content?.parts) {
chunk.candidates[0].content.parts.forEach(part => {
if (part.text) finalOutput += part.text;
});
}
});
}
if (finalOutput) {
console.log("▶️ Posting to Google Chat...");
const chatOptions = {
"method": "post",
"contentType": "application/json",
"payload": JSON.stringify({ "text": finalOutput }),
"muteHttpExceptions": true
};
UrlFetchApp.fetch(webhookUrl, chatOptions);
console.log("Success.");
} else {
console.log("Gemini returned no text content.");
}
} catch (e) {
console.error("Error in postGeminiToSpace:", e);
}
}