Could not find 'payer-view' link

53 views

Skip to first unread message

Helen Butcher

unread,

Apr 30, 2025, 6:59:45 AMApr 30

to Google Apps Script Community

I am trying to put together an automated system for my not-for-profit village Christmas Market using Google Forms, Sheets, mailmerge and GMail.

The Form collects all the information they need throughout the whole admin system.

A Master Google Sheet is created with all this info.

Stall Allocation is a Google Sheet which pulls required info from the Master sheet to then create Papal Payment button links which are saved back into the Stall Allocation sheet with payment reminder after 4 days and payment link end date after 7 days and a Paid/Unpaid column (which also updates in the Master sheet)

The Google payment button needs to be sent to each customer as part of a mail merged email with relevant information for each customer and showing as sent in Stall Allocation and Master sheet.

I have spent the last week trying to work out the Apps Script to make this happen. My latest attempt was put together with Gemini 2.5 Pro (experimental) as I just got so tied up trying to create my own code - Forgive Me please.

This code is showing the following error:

This is the code :

// ==========================================
// CONFIGURATION
// ==========================================

// --- --- --- --- --- --- --- --- --- --- --- ---
// --- EDIT THESE VALUES TO MATCH YOUR SETUP ---
// --- --- --- --- --- --- --- --- --- --- --- ---

const CONFIG = {
  // Set to true for testing with PayPal Sandbox, false for Live transactions
  USE_SANDBOX: true,
  // Your default currency code (e.g., "USD", "EUR", "GBP")
  CURRENCY_CODE: "GBP",
  // Number of days from creation until the invoice is due (Link expiration)
  DAYS_UNTIL_DUE: 7,
  // Set to true to enable automatic reminders from PayPal (relative to due date)
  // Check PayPal docs for specific reminder configurations if needed.
  ENABLE_PAYPAL_REMINDERS: true,
  // Column to write status messages (e.g., "Link Created", "Error: ...")
  // Set to -1 if you don't want a status column.
  STATUS_COLUMN_INDEX: 10, // Example: Column J (if A=1, B=2, etc.)
  // Optional: Company Name to appear on Invoice (configure fully in PayPal account)
  INVOICER_BUSINESS_NAME: "Heighington Christmas Market",
  // Optional: Header row number (to skip processing)
  HEADER_ROW: 1
};

const COLUMN_MAPPING = {
  // --- Column numbers (A=1, B=2, C=3, etc.) ---
  INVOICE_NUM: 1,   // Column containing Your Invoice Number
  EMAIL: 2,         // Column containing Customer Email
  PRODUCT_DESC: 3,  // Column containing Product Description
  PRICE: 4,         // Column containing Price
  PAYPAL_LINK: 5    // Column where the PayPal link will be written
};

// --- --- --- --- --- --- --- --- --- --- --- ---
// --- END OF USER CONFIGURATION ---
// --- --- --- --- --- --- --- --- --- --- --- ---
// ==========================================

// PayPal API Endpoints
const PAYPAL_API_BASE = CONFIG.USE_SANDBOX ? 'https://api-m.sandbox.paypal.com' : 'https://api-m.paypal.com';
const PAYPAL_TOKEN_URL = `${PAYPAL_API_BASE}/v1/oauth2/token`;
const PAYPAL_INVOICE_URL = `${PAYPAL_API_BASE}/v2/invoicing/invoices`;

// --- Script Properties Keys ---
const PROP_CLIENT_ID = 'PAYPAL_CLIENT_ID';
const PROP_CLIENT_SECRET = 'PAYPAL_CLIENT_SECRET';

// ==========================================
// MENU SETUP
// ==========================================

/**
 * Runs when the spreadsheet is opened and adds a custom menu.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('PayPal Tools')
    .addItem('1. Setup Credentials', 'setupPayPalCredentials')
    .addSeparator()
    .addItem('Create Links (Selected Rows)', 'createPayPalLinksForSelectedRows')
    .addItem('Create Links (All Rows)', 'createPayPalLinksForAllRows')
    .addToUi();
}

/**
 * Prompts user for PayPal API credentials and stores them securely.
 * Run this ONCE from the Script Editor.
 */
function setupPayPalCredentials() {
  const ui = SpreadsheetApp.getUi();
  const scriptProperties = PropertiesService.getScriptProperties();

  const clientId = ui.prompt('Enter PayPal API Client ID:', ui.ButtonSet.OK_CANCEL);
  if (clientId.getSelectedButton() !== ui.Button.OK || !clientId.getResponseText()) {
    ui.alert('Setup cancelled.');
    return;
  }
  scriptProperties.setProperty(PROP_CLIENT_ID, clientId.getResponseText().trim());

  const clientSecret = ui.prompt('Enter PayPal API Client Secret:', ui.ButtonSet.OK_CANCEL);
  if (clientSecret.getSelectedButton() !== ui.Button.OK || !clientSecret.getResponseText()) {
    // Consider deleting the client ID if secret setup fails halfway
    scriptProperties.deleteProperty(PROP_CLIENT_ID);
    ui.alert('Setup cancelled.');
    return;
  }
  scriptProperties.setProperty(PROP_CLIENT_SECRET, clientSecret.getResponseText().trim());

  ui.alert('PayPal credentials stored successfully in Script Properties.');
}


// ==========================================
// MAIN PROCESSING FUNCTIONS
// ==========================================

/**
 * Processes only the rows currently selected by the user.
 */
function createPayPalLinksForSelectedRows() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const selectedRanges = sheet.getActiveRangeList().getRanges();
  const token = getPayPalAccessToken_(); // Get token once for potentially multiple rows

  if (!token) return; // Error handled in getPayPalAccessToken_

  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutput('<p>Processing selected rows...</p>').setTitle('Processing'));

  for (const range of selectedRanges) {
    for (let i = 1; i <= range.getNumRows(); i++) {
      const rowNum = range.getRow() + i - 1;
      if (rowNum <= CONFIG.HEADER_ROW) continue; // Skip header
      processRow_(sheet, rowNum, token);
    }
  }
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutput('<p>Processing complete.</p>').setTitle('Finished'));
  Logger.log('Finished processing selected rows.');
}

/**
 * Processes all rows in the sheet that have data and haven't been processed.
 */
function createPayPalLinksForAllRows() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const dataRange = sheet.getDataRange();
  const lastRow = dataRange.getLastRow();
  const token = getPayPalAccessToken_(); // Get token once

  if (!token) return; // Error handled in getPayPalAccessToken_

  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutput('<p>Processing all rows...</p>').setTitle('Processing'));

  for (let rowNum = CONFIG.HEADER_ROW + 1; rowNum <= lastRow; rowNum++) {
    processRow_(sheet, rowNum, token);
  }
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutput('<p>Processing complete.</p>').setTitle('Finished'));
  Logger.log('Finished processing all rows.');
}

/**
 * Processes a single row: reads data, creates invoice, writes link/status.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet The sheet object.
 * @param {number} rowNum The row number to process.
 * @param {string} accessToken The PayPal API access token.
 */
function processRow_(sheet, rowNum, accessToken) {
  try {
    // --- 1. Read Data & Check if Already Processed ---
    const linkCell = sheet.getRange(rowNum, COLUMN_MAPPING.PAYPAL_LINK);
    if (linkCell.getValue() !== '') {
      Logger.log(`Skipping row ${rowNum}: Link already exists.`);
      // Optionally update status if you want: updateStatus_(sheet, rowNum, "Skipped: Link exists");
      return;
    }

    const email = sheet.getRange(rowNum, COLUMN_MAPPING.EMAIL).getValue();
    const invoiceNum = sheet.getRange(rowNum, COLUMN_MAPPING.INVOICE_NUM).getValue();
    const productDesc = sheet.getRange(rowNum, COLUMN_MAPPING.PRODUCT_DESC).getValue();
    const price = sheet.getRange(rowNum, COLUMN_MAPPING.PRICE).getValue();

    // Basic validation
    if (!email || !invoiceNum || !productDesc || !price || typeof price !== 'number' || price <= 0) {
       Logger.log(`Skipping row ${rowNum}: Missing or invalid data.`);
       updateStatus_(sheet, rowNum, "Error: Missing/Invalid Data");
       return;
    }

    // --- 2. Prepare Invoice Data ---
    const invoiceData = {
      email: email,
      invoiceNumber: invoiceNum.toString(), // Ensure it's a string
      productDescription: productDesc,
      price: price.toFixed(2) // Ensure 2 decimal places
    };

    // --- 3. Create and Send Invoice via PayPal API ---
    const paymentLink = createAndSendPayPalInvoice_(accessToken, invoiceData);

    // --- 4. Update Sheet ---
    if (paymentLink) {
      linkCell.setValue(paymentLink);
      updateStatus_(sheet, rowNum, `Link Created: ${new Date().toLocaleDateString()}`);
      Logger.log(`Successfully created link for row ${rowNum}`);
    } else {
      // Error message should have been logged within createAndSendPayPalInvoice_
      updateStatus_(sheet, rowNum, "Error: Failed to create link (see Logs)");
    }

  } catch (error) {
    Logger.log(`Error processing row ${rowNum}: ${error} \n Stack: ${error.stack}`);
    updateStatus_(sheet, rowNum, `Error: ${error.message}`);
  }
}

// ==========================================
// PAYPAL API HELPER FUNCTIONS
// ==========================================

/**
 * Retrieves PayPal credentials and obtains an OAuth2 access token.
 * @return {string|null} The access token or null if an error occurs.
 * @private
 */
function getPayPalAccessToken_() {
  const scriptProperties = PropertiesService.getScriptProperties();
  const clientId = scriptProperties.getProperty(PROP_CLIENT_ID);
  const clientSecret = scriptProperties.getProperty(PROP_CLIENT_SECRET);

  if (!clientId || !clientSecret) {
    Logger.log('PayPal Client ID or Secret not set. Please run "Setup Credentials" first.');
    SpreadsheetApp.getUi().alert('PayPal Client ID or Secret not set. Please run "Setup Credentials" from the PayPal Tools menu first.');
    return null;
  }

  const options = {
    method: 'post',
    headers: {
      'Authorization': 'Basic ' + Utilities.base64Encode(clientId + ':' + clientSecret),
      'Accept': 'application/json',
      'Accept-Language': 'en_US'
    },
    payload: {
      'grant_type': 'client_credentials'
    },
    muteHttpExceptions: true // Prevent script stopping on API errors, handle manually
  };

  try {
    const response = UrlFetchApp.fetch(PAYPAL_TOKEN_URL, options);
    const responseCode = response.getResponseCode();
    const responseBody = response.getContentText();

    if (responseCode === 200) {
      const jsonResponse = JSON.parse(responseBody);
      Logger.log('Successfully obtained PayPal Access Token.');
      return jsonResponse.access_token;
    } else {
      Logger.log(`Error getting PayPal token. Status: ${responseCode}, Response: ${responseBody}`);
      SpreadsheetApp.getUi().alert(`Error getting PayPal token. Status: ${responseCode}. Check Logs for details.`);
      return null;
    }
  } catch (error) {
    Logger.log(`Failed to fetch PayPal token: ${error}`);
    SpreadsheetApp.getUi().alert(`Failed to fetch PayPal token: ${error.message}. Check Logs.`);
    return null;
  }
}

/**
 * Creates a draft invoice, sends it, and returns the payer view link.
 * @param {string} accessToken The valid PayPal OAuth2 access token.
 * @param {object} invoiceData Object containing email, invoiceNumber, productDescription, price.
 * @return {string|null} The payment link or null on failure.
 * @private
 */
function createAndSendPayPalInvoice_(accessToken, invoiceData) {
  // --- 1. Calculate Due Date ---
  const issueDate = new Date();
  const dueDate = new Date(issueDate);
  dueDate.setDate(issueDate.getDate() + CONFIG.DAYS_UNTIL_DUE);
  const formattedDueDate = Utilities.formatDate(dueDate, Session.getScriptTimeZone(), "yyyy-MM-dd");
  const formattedIssueDate = Utilities.formatDate(issueDate, Session.getScriptTimeZone(), "yyyy-MM-dd");

  // --- 2. Construct Invoice Payload ---
  const payload = {
    detail: {
      invoice_number: invoiceData.invoiceNumber,
      invoice_date: formattedIssueDate,
      currency_code: CONFIG.CURRENCY_CODE,
      payment_term: {
        term_type: "DUE_ON_DATE_SPECIFIED",
        due_date: formattedDueDate
      },
      // Optional Note
      // note: "Thank you for your business."
    },
    invoicer: {
      // You might need to fill more details here based on your PayPal setup
       Heighington_Christmas_Market: CONFIG.INVOICER_BUSINESS_NAME
      // email_address: "bl...@hotmail.com", // Usually not needed if authenticated correctly
    },
    primary_recipients: [
      {
        billing_info: {
          email_address: invoiceData.email
          // You can add more recipient details like name, address if available
          // name: { given_name: "John", surname: "Doe" }
        }
      }
    ],
    items: [
      {
        name: invoiceData.productDescription,
        quantity: "1", // Assuming quantity is always 1
        unit_amount: {
          currency_code: CONFIG.CURRENCY_CODE,
          value: invoiceData.price
        }
        // unit_of_measure: "QUANTITY" // Or HOURS, AMOUNT etc.
      }
    ],
    // Amount needs to be calculated based on items, tax, discounts etc.
    // For simple cases, PayPal often calculates it if items are provided.
    // If you have tax/discounts, add them to items or the 'amount' section explicitly.
    // amount: {
    //   currency_code: CONFIG.CURRENCY_CODE,
    //   value: invoiceData.price // Simple case, no tax/discount
    // },
    configuration: {
      // partial_payment: { allow_partial_payment: false }, // default is false
      // allow_tip: false, // default is false
      tax_inclusive: false // default
    }
  };


  // --- 3. Create Draft Invoice Request ---
  const optionsCreate = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + accessToken
      // 'PayPal-Request-Id': 'INV-' + invoiceData.invoiceNumber + '-' + Date.now() // Optional: For tracking
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };

  try {
    Logger.log(`Creating draft invoice for: ${invoiceData.invoiceNumber}`);
    const responseCreate = UrlFetchApp.fetch(PAYPAL_INVOICE_URL, optionsCreate);
    const responseCodeCreate = responseCreate.getResponseCode();
    const responseBodyCreate = responseCreate.getContentText();

    if (responseCodeCreate === 201) { // 201 Created for draft invoice
      const jsonResponseCreate = JSON.parse(responseBodyCreate);
      const invoiceId = jsonResponseCreate.id;
      const invoiceHref = jsonResponseCreate.href; // URL of the created invoice resource
      Logger.log(`Draft invoice created successfully. ID: ${invoiceId}`);

      // --- 4. Send the Invoice ---
      const sendUrl = `${invoiceHref}/send`; // Use HATEOAS link from response
      // Alternative fixed URL: const sendUrl = `${PAYPAL_INVOICE_URL}/${invoiceId}/send`;

      const optionsSend = {
        method: 'post',
        contentType: 'application/json',
        headers: {
          'Authorization': 'Bearer ' + accessToken
        },
        // Body for send can include subject, note etc., or be empty {}
        payload: JSON.stringify({
           send_to_invoicer: false, // Send only to recipient?
           send_to_recipient: true,
           subject: `Invoice ${invoiceData.invoiceNumber} from ${CONFIG.INVOICER_BUSINESS_NAME || 'Your Company'}`,
           note: `Invoice ${invoiceData.invoiceNumber}. Payment due by ${formattedDueDate}.`
        }),
        muteHttpExceptions: true
      };

      Logger.log(`Sending invoice ID: ${invoiceId}`);
      const responseSend = UrlFetchApp.fetch(sendUrl, optionsSend);
      const responseCodeSend = responseSend.getResponseCode();
      const responseBodySend = responseSend.getContentText();

      if (responseCodeSend === 200 || responseCodeSend === 202 ) { // 200 OK or 202 Accepted for send
         Logger.log(`Invoice ${invoiceId} sent successfully.`);
         // The payer view link is usually in the response body after sending
         const jsonResponseSend = JSON.parse(responseBodySend);

         if (jsonResponseSend && jsonResponseSend.href) {
            // Sometimes the Send response directly contains the payer link
            // Need to verify exact structure from PayPal Sandbox response
            // It might be under a different key or require a subsequent GET request
            Logger.log("Send response: " + responseBodySend); // Log to inspect

            // --- 5. (Alternative/Robust) Get Invoice Details to find Payer Link ---
            // If the send response doesn't reliably contain the link, fetch the invoice details again.
            const optionsGet = {
                method: 'get',
                headers: { 'Authorization': 'Bearer ' + accessToken },
                muteHttpExceptions: true
            };
            const responseGet = UrlFetchApp.fetch(invoiceHref, optionsGet);
            if (responseGet.getResponseCode() === 200) {
                const invoiceDetails = JSON.parse(responseGet.getContentText());
                if (invoiceDetails.links) {
                    const payerLinkObj = invoiceDetails.links.find(link => link.rel === 'payer-view');
                    if (payerLinkObj && payerLinkObj.href) {
                        Logger.log(`Found payer link for ${invoiceId}: ${payerLinkObj.href}`);
                        return payerLinkObj.href;
                    }
                }
            }
             Logger.log(`Could not find 'payer-view' link after sending invoice ${invoiceId}. Check GET response or Send Response structure in logs.`);
             return null; // Link not found reliably

         } else {
            Logger.log(`Invoice ${invoiceId} sent, but Send response did not contain expected link structure. Body: ${responseBodySend}`);
             // Fallback to trying GET request
             // This part is duplicated from above block, could be refactored.
              const optionsGet = {
                method: 'get',
                headers: { 'Authorization': 'Bearer ' + accessToken },
                muteHttpExceptions: true
              };
              const responseGet = UrlFetchApp.fetch(invoiceHref, optionsGet);
              if (responseGet.getResponseCode() === 200) {
                const invoiceDetails = JSON.parse(responseGet.getContentText());
                if (invoiceDetails.links) {
                    const payerLinkObj = invoiceDetails.links.find(link => link.rel === 'payer-view');
                    if (payerLinkObj && payerLinkObj.href) {
                        Logger.log(`Found payer link for ${invoiceId} via GET: ${payerLinkObj.href}`);
                        return payerLinkObj.href;
                    }
                }
              }
            Logger.log(`Could not find 'payer-view' link after sending invoice ${invoiceId} via Send response or subsequent GET. Check Logs.`);
            return null;
         }

      } else {
        Logger.log(`Error sending PayPal invoice ${invoiceId}. Status: ${responseCodeSend}, Response: ${responseBodySend}`);
        return null;
      }

    } else {
      Logger.log(`Error creating PayPal draft invoice. Status: ${responseCodeCreate}, Response: ${responseBodyCreate}`);
      return null;
    }
  } catch (error) {
    Logger.log(`Failed during PayPal invoice creation/sending: ${error} \n Stack: ${error.stack}`);
    return null;
  }
}


/**
 * Updates the status column for a given row.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet The sheet object.
 * @param {number} rowNum The row number to update.
 * @param {string} message The status message to write.
 * @private
 */
function updateStatus_(sheet, rowNum, message) {
  if (CONFIG.STATUS_COLUMN_INDEX > 0) {
    sheet.getRange(rowNum, CONFIG.STATUS_COLUMN_INDEX).setValue(message);
  }
}

I would greatly appreciate if someone can cast an eye over the code and let me know what I'm doing wrong.

Please and Thank you

George Ghanem

unread,

Apr 30, 2025, 7:48:44 PMApr 30

to google-apps-sc...@googlegroups.com

Hi Helen,

From the error and the code you provided, it looks like the PayPal links creation is not working correctly. Not easy to tell whether it is your PayPal credentials or the response from PayPal is not providing the correct response.

I use PayPal on my website, but I had setup just a static link with no amount for users to use. Would something like that work for you? It would simplify your code greatly if you can just use a static link for all.

I can help you modify the code, but maybe it is best if we take this offline, just reply back to my email address directly.

George

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/f6dace7a-6030-4deb-9773-c0a9d407d84dn%40googlegroups.com.

Helen Butcher

unread,

May 1, 2025, 5:09:14 PMMay 1

to Google Apps Script Community

Thanks so much for your reply George.

I really must be tired - I cannot work out where to find your email??

I have managed to get the Paypal Link creation to work but am now struggling with trying to get the Paypal IPN to keep the google sheet up to date regarding payment.

Is there a way of telling me how to find your email!! - alternatively I see my email is visible if you wanted to email me and then I can get back to you :-)

Thanks again

Google Pony

unread,

May 8, 2025, 10:08:29 AMMay 8

to Google Apps Script Community

Hello, Helen.

Thank you for sharing your code and describing your workflow. The solution provided below addresses both the payer-view link issue and the IPN (Instant Payment Notification) connection for your Christmas Market system.

1. Resolving the "Could Not Find 'Payer-view' Link" Error

The problem arises because the PayPal API response structure differs between the sandbox and live environments. Here's an improved createAndSendPayPalInvoice_() method that properly extracts the payer link.

/**
 * Improved version to handle PayPal API response variability
 */
function createAndSendPayPalInvoice_(accessToken, invoiceData) {
  try {
    // ... (keep existing payload and draft invoice creation code) ...

    // After sending the invoice, fetch it again to get the payer link
    const invoiceDetailsUrl = `${PAYPAL_INVOICE_URL}/${invoiceId}`;

    const optionsGet = {
      method: 'get',
      headers: { 'Authorization': 'Bearer ' + accessToken },
      muteHttpExceptions: true
    };

    const responseGet = UrlFetchApp.fetch(invoiceDetailsUrl, optionsGet);

    if (responseGet.getResponseCode() === 200) {
      const invoiceDetails = JSON.parse(responseGet.getContentText());
      

      // Method 1: Check 'links' array for 'payer-view'
      const payerLinkObj = invoiceDetails.links?.find(link => link.rel === 'payer-view');
      if (payerLinkObj?.href) return payerLinkObj.href;

      // Method 2: Fallback to 'payer_view_url' (sandbox sometimes uses this)
      if (invoiceDetails.payer_view_url) return invoiceDetails.payer_view_url;

      throw new Error("Payer link not found in PayPal response. Check logs for full response.");
    }
  } catch (error) {
    Logger.log(`Error fetching payer link: ${error}\nResponse: ${responseGet?.getContentText()}`);
    return null;
  }
}

Key Fixes:

Handles both links[].rel and payer_view_url response formats.
Logs full API responses for debugging.

2. Setting Up PayPal IPN for Real-Time Updates
To track payments automatically:

Step 1: Configure IPN in PayPal

Go to PayPal IPN Settings.
Enable IPN and set the Notification URL to your Apps Script Web App URL (deploy as a web app with doPost()).

Step 2: IPN Handler Script

/**
 * Deploy this as a Web App to handle PayPal IPN callbacks
 */
function doPost(e) {
  const VERIFY_URL = CONFIG.USE_SANDBOX ? 
    'https://ipnpb.sandbox.paypal.com/cgi-bin/webscr' : 
    'https://ipnpb.paypal.com/cgi-bin/webscr';

  // Verify the IPN message with PayPal
  const payload = `cmd=_notify-validate&${e.postData.contents}`;
  const validation = UrlFetchApp.fetch(VERIFY_URL, {
    method: 'POST',
    payload: payload,
    muteHttpExceptions: true
  });

  if (validation.getContentText() === 'VERIFIED') {
    const data = parseIpnData(e.postData.contents);
    updateSheetWithPayment(data); // Implement this to update your sheet
    return ContentService.createTextOutput('OK');
  } else {
    Logger.log(`Invalid IPN: ${e.postData.contents}`);
    return ContentService.createTextOutput('INVALID');
  }
}

/**
 * Parses raw IPN data into an object
 */
function parseIpnData(rawData) {
  return Object.fromEntries(
    rawData.split('&').map(item => {
      const [key, value] = item.split('=');
      return [key, decodeURIComponent(value)];
    })
  );
}

/**
 * Updates the sheet with payment status
 */
function updateSheetWithPayment(ipnData) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Master');
  const invoiceNum = ipnData.invoice; // Match your PayPal invoice field
  const row = findRowByInvoice(sheet, invoiceNum);

  if (row) {
    sheet.getRange(row, PAID_COLUMN_INDEX).setValue('PAID');
    sheet.getRange(row, PAYMENT_DATE_COLUMN_INDEX).setValue(new Date());
  }
}

Key Features:

Real-time updates: Marks rows as "PAID" automatically.
Security: Validates IPN messages with PayPal.

3. Alternative: Static PayPal Buttons (Simpler Approach):

If dynamic links are overkill, use static buttons with predefined amounts:

<!-- Template for mail-merge emails -->
<a href="https://www.paypal.com/cgi-bin/webscr?cmd=_xclick&business=YOUR_EMAIL&amount=AMOUNT&item_name=ITEM_DESC&invoice=INVOICE_NUM">
  Pay Now
</a>

Pros:

No API calls needed.
Works with IPN for payment tracking.

Next Steps:

Test the Payer Link Fix:
- Verify in both sandbox and live modes.
Deploy IPN:
- Deploy your script as a web app (Publish → Deploy as Web App).
Optional:
- I can share a complete sample project if needed.

Would you like assistance with any specific part?

Sincerely yours,

Sandeep Kumar Vollala

Consultant

Reply all

Reply to author

Forward

0 new messages