How to Efficiently Organize Business Card Data with ChatGPT API: Leveraging Google Sheets and AI

// Google Apps Script to Use ChatGPT API for Parsing Business Card Information

// Function to parse business card information
function parseBusinessCard() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const dataRange = sheet.getRange("A2:A" + sheet.getLastRow());
  const data = dataRange.getValues();

  data.forEach((row, index) => {
    const cardInfo = row[0];
    const rowIndex = index + 2; // Adjust for the header row
    const isRowAlreadyParsed = sheet.getRange(`B${rowIndex}:L${rowIndex}`).getValues()[0].some(cell => cell !== "");

    // Process only rows with data in Column A and not already parsed
    if (cardInfo && !isRowAlreadyParsed) {
      const parsedData = getChatGPTParsedData(sheet, cardInfo);
      updateSheetWithParsedData(sheet, rowIndex, parsedData);
    }
  });
}

// Function to retrieve parsed data from ChatGPT API
function getChatGPTParsedData(sheet, cardInfo) {
  const apiKey = PropertiesService.getScriptProperties().getProperty("OPENAI_API_KEY");
  const url = "https://api.openai.com/v1/chat/completions";
  const headers = sheet.getRange("A1:L1").getValues()[0];
  const headersJson = headers.reduce((acc, header) => {
    acc[header] = ""; // Initialize JSON structure with empty values
    return acc;
  }, {});

  const messages = [
    {
      "role": "user",
      "content": `Please parse the following business card information into the specified JSON format: ${JSON.stringify(headersJson)}: ${cardInfo}`
    }
  ];

  const options = {
    "method": "post",
    "headers": {
      "Authorization": `Bearer ${apiKey}`,
      "Content-Type": "application/json"
    },
    "payload": JSON.stringify({
      "model": "gpt-4",
      "messages": messages,
      "max_tokens": 500,
      "temperature": 0.7
    })
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const json = JSON.parse(response.getContentText());
    const parsedData = JSON.parse(json.choices[0].message.content.trim());
    return parsedData;
  } catch (error) {
    Logger.log("Error while fetching data from ChatGPT API: " + error);
    return null;
  }
}

// Function to update the spreadsheet with parsed data
function updateSheetWithParsedData(sheet, rowIndex, parsedData) {
  if (parsedData) {
    const headers = sheet.getRange("A1:L1").getValues()[0];
    headers.forEach((header, index) => {
      sheet.getRange(rowIndex, index + 1).setValue(parsedData[header] || ""); // Populate columns with parsed data
    });
  }
}

// Function to set the ChatGPT API key in script properties
function setApiKey() {
  const apiKey = "YOUR_API_KEY_HERE";
  PropertiesService.getScriptProperties().setProperty("OPENAI_API_KEY", apiKey);
}