Google Sheets Scheduler for Farcaster

gistfile1.txt

/**
 * Warpcast Scheduler for Google Sheets
 *
 * This script polls a Google Sheet and schedules posts to be sent via an API
 * at the times specified in the "Post Time" column.
 */

// Configuration
const API_URL = 'MY API URL';
const API_KEY = PropertiesService.getScriptProperties().getProperty('APIKey');
const SHEET_NAME = 'Scheduler'; // Name of the sheet containing the data
const CHECK_INTERVAL_MINUTES = 5; // How often to check for posts to publish
const TIMEZONE = 'America/Chicago'; // Austin, TX timezone
const STATUS_COLUMN_HEADER = 'Status'; // Header for the status column


/**
 * Sets up triggers when the spreadsheet is opened
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Warpcast Scheduler')
    .addItem('Start Scheduler', 'setupTrigger')
    .addItem('Stop Scheduler', 'removeTrigger')
    .addItem('Run Once Now', 'checkAndSendPosts')
    .addItem('Test API Connection', 'testApiConnection')
    .addItem('Add Test Row', 'addTestRow')
    .addToUi();

  // Show a welcome message
  ui.alert(
    'Warpcast Scheduler',
    'Use the "Warpcast Scheduler" menu to start/stop the automatic posting.',
    ui.ButtonSet.OK
  );
}

/**
 * Sets up a time-based trigger to run the checkAndSendPosts function
 */
function setupTrigger() {
  // Delete any existing triggers first
  removeTrigger();

  // Create a new trigger to run every X minutes
  ScriptApp.newTrigger('checkAndSendPosts')
    .timeBased()
    .everyMinutes(CHECK_INTERVAL_MINUTES)
    .create();

  SpreadsheetApp.getActive().toast(
    'Scheduler is now running. It will check for posts every ' +
      CHECK_INTERVAL_MINUTES +
      ' minutes.'
  );
}

/**
 * Removes all triggers
 */
function removeTrigger() {
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(trigger => {
    if (trigger.getHandlerFunction() === 'checkAndSendPosts') {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  SpreadsheetApp.getActive().toast('Scheduler stopped');
}

/**
 * Checks for posts that need to be sent and sends them
 */
function checkAndSendPosts() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  if (!sheet) {
    console.error(`Sheet "${SHEET_NAME}" not found`);
    return;
  }

  // Get all data from the sheet
  const data = sheet.getDataRange().getValues();
  if (data.length <= 1) return; // Only headers or empty sheet

  // Find column indices using a helper function
  const getColumnIndex = (header) => data[0].indexOf(header);
  const castColIndex = getColumnIndex('Cast');
  const timeColIndex = getColumnIndex('Post Time');
  let statusColIndex = getColumnIndex(STATUS_COLUMN_HEADER); // Use the constant

  // Add Status column if it doesn't exist
  if (statusColIndex === -1) {
    statusColIndex = data[0].length;  // Index of the new column
    sheet.getRange(1, statusColIndex + 1).setValue(STATUS_COLUMN_HEADER); // Use the constant
    data[0].push(STATUS_COLUMN_HEADER); // Update the headers array
  }

  // Check if required columns exist
  if (castColIndex === -1 || timeColIndex === -1) {
    console.error('Required columns "Cast" and/or "Post Time" not found');
    return;
  }

  // Current time in your preferred timezone
  const now = new Date();
  const nowAustin = new Date(now.toLocaleString('en-US', { timeZone: TIMEZONE }));

  // Process each row (skip header)
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const cast = row[castColIndex];
    const postTimeCell = row[timeColIndex];
    let status = row[statusColIndex]; // No need to check for undefined here.

    // Skip if already posted or no content
    if (status === 'Posted' || !cast) continue;

    // Parse the post time
    let postTime;
    if (postTimeCell instanceof Date) {
      postTime = postTimeCell;
    } else if (typeof postTimeCell === 'string' && postTimeCell.trim()) {
      // Try to parse string to date
      try {
        postTime = new Date(postTimeCell);
      } catch (e) {
        // Update status to error.  Set status *before* continuing.
        const statusCell = sheet.getRange(i + 1, statusColIndex + 1);
        statusCell.setValue('Error: Invalid date format');
        continue;
      }
    } else {
      continue; // No valid post time
    }

    // Check if it's time to post
    if (postTime <= nowAustin) {
      const success = sendPost(cast);

      // Update status
      const statusCell = sheet.getRange(i + 1, statusColIndex + 1); // Use cached statusColIndex
      const timestamp = nowAustin.toLocaleString('en-US', { timeZone: TIMEZONE }); // Consistent timezone

      if (success) {
        statusCell.setValue('Posted at ' + timestamp);
      } else {
        statusCell.setValue('Failed at ' + timestamp);
      }
    }
  }
}

/**
 * Sends a post to the API
 *
 * @param {string} text - The text to post
 * @return {boolean} - Whether the post was successful
 */
function sendPost(text) {
  try {
    // Clean the text input to avoid any special characters or formatting issues
    const cleanText = text.toString().trim();
    
    // Create the payload exactly as in the curl command
    const payload = JSON.stringify({
      text: cleanText
    });

    console.log("Sending payload:", payload);

    const options = {
      method: 'post',
      contentType: 'application/json',
      payload: payload,
      muteHttpExceptions: true,
      // Add explicit headers to match curl command exactly
      headers: {
        'Content-Type': 'application/json',
        'x-api-key': API_KEY
      }
    };

    const response = UrlFetchApp.fetch(API_URL, options);
    const code = response.getResponseCode();
    const content = response.getContentText();

    console.log("Response code:", code);
    console.log("Response content:", content);

    if (code >= 200 && code < 300) {
      console.log('Successfully posted: ' + cleanText);
      return true;
    } else {
      console.error('Failed to post. Status code: ' + code);
      console.error('Response: ' + content);
      return false;
    }
  } catch (e) {
    console.error('Error sending post: ' + e.toString());
    return false;
  }
}

/**
 * Test function to manually validate the API connection
 * Run this function to test with a sample post
 */
function testApiConnection() {
  const testText = "Testing an API route for programmatically casting";
  console.log("Testing API with text: " + testText);
  
  const success = sendPost(testText);
  
  if (success) {
    console.log("API test successful!");
    // Also show a visible notification in the spreadsheet
    SpreadsheetApp.getActive().toast("API test successful!", "Test Result", 10);
  } else {
    console.error("API test failed");
    SpreadsheetApp.getActive().toast("API test failed. Check logs for details.", "Test Result", 10);
  }
  
  return success;
}

/**
 * Adds a test row to the sheet (for debugging)
 */
function addTestRow() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  if (!sheet) {
    console.error(`Sheet "${SHEET_NAME}" not found`);
    SpreadsheetApp.getActive().toast(`Sheet "${SHEET_NAME}" not found`, "Error", 10);
    return;
  }
  
  console.log("Adding test row to sheet");

  // Create a timestamp 2 minutes from now (in Austin time)
  const now = new Date();
  const nowAustin = new Date(now.toLocaleString('en-US', { timeZone: TIMEZONE }));
  const testTime = new Date(nowAustin.getTime() + 2 * 60000); // Add 2 minutes

  // Get column indices
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const castColIndex = headers.indexOf('Cast');
  const timeColIndex = headers.indexOf('Post Time');
  
  if (castColIndex === -1 || timeColIndex === -1) {
    console.error('Required columns "Cast" and/or "Post Time" not found');
    SpreadsheetApp.getActive().toast('Required columns "Cast" and/or "Post Time" not found', "Error", 10);
    return;
  }
  
  // Format the test data as an array with values in the correct positions
  const rowData = Array(Math.max(castColIndex, timeColIndex) + 1).fill("");
  rowData[castColIndex] = 'Test post from Google Apps Script at ' + nowAustin.toLocaleString();
  rowData[timeColIndex] = testTime;
  
  // Add the row
  sheet.appendRow(rowData);
  
  SpreadsheetApp.getActive().toast(`Test row added. Scheduled for: ${testTime.toLocaleString()}`, "Success", 10);
  console.log("Test row added successfully");
}