gemini_seo.gs

const properties = PropertiesService.getScriptProperties().getProperties();
const geminiApiKey = properties['GOOGLE_API_KEY'];
const geminiEndpoint = `https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-pro-latest:generateContent?key=${geminiApiKey}`;

function callGemini(prompt, temperature=0) {
  const payload = {
    "contents": [
      {
        "parts": [
          {
            "text": prompt
          },
        ]
      }
    ], 
    "generationConfig":  {
      "temperature": temperature,
    },
  };

  const options = { 
    'method' : 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(payload)
  };

  const response = UrlFetchApp.fetch(geminiEndpoint, options);
  const data = JSON.parse(response);
  const content = data["candidates"][0]["content"]["parts"][0]["text"];
  return content;
}

function testGemini() {
  const prompt = "The best thing since sliced bread is";
  const output = callGemini(prompt);
  console.log(prompt, output);
}


function generateBulletPoints(description, seoKeywords) {
  // Construct the prompt for Gemini
  const prompt = `Based on the following product description and SEO keywords, generate **exactly 5 concise bullet points:**:

  **Product Description:**
  ${description}

  **SEO Keywords:**
  ${seoKeywords}

  **Bullet Points:**
  1. **Packaging Content:** [Generate a bullet point focused on packaging content]
  2. [Generate a bullet point incorporating SEO keywords]
  3. [Generate a bullet point incorporating SEO keywords]
  4. [Generate a bullet point incorporating SEO keywords]
  5. [Generate a bullet point incorporating SEO keywords]`;

  // Call the Gemini API
  const response = callGemini(prompt);
 
  // Split into lines and filter out empty lines
  const bulletPoints = response.split('\n').filter(line => line.trim() !== "");

  // Clean each bullet point by removing leading asterisks and bold formatting
  const cleanedBulletPoints = bulletPoints.map(point => {
      let cleanedPoint = point.trim();

      // Remove asterisks and bold formatting (more robust)
      cleanedPoint = cleanedPoint.replace(/\*+/g, '').replace(/\*\*/g, '').trim(); //Remove any * and **
      return cleanedPoint;
    });

  return cleanedBulletPoints;
}


function generateBulletPointsForRange() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange("F2:G402"); // Adjust the range as needed
  const values = range.getValues();
  
  const startingColumn = 8; // Column H (adjust based on your sheet)
  
  values.forEach((row, index) => {
    const description = row[0];
    const seoKeywords = row[1];

    // Skip if description or seoKeywords are empty
    if (!description || !seoKeywords) {
      Logger.log(`Row ${index + 2} skipped due to empty data.`);
      return;
    }
    
    // Generate bullet points
    const bulletPoints = generateBulletPoints(description, seoKeywords);

    // Calculate the row where data will be written (F2 starts at index = 0)
    const targetRow = index + 2; // +2 because data starts at row 2
    const numColumns = bulletPoints.length;

    // Check for column overflow (H = 8, max is L = 12)
    if (startingColumn + numColumns - 1 > 12) {
      Logger.log(`Row ${targetRow}: Not enough columns to write all bullet points.`);
      return;
    }

    // Write bullet points to the sheet
    const targetRange = sheet.getRange(targetRow, startingColumn, 1, numColumns);
    targetRange.setValues([bulletPoints]); // Wrap in an array for row format

    Logger.log(`Bullet points written to row ${targetRow}.`);
  });
}