<?php

$secret = getenv('PHOTOCOLLECT_DEEPLINK_SECRET');
if ($secret === false || $secret === '') {
    throw new RuntimeException('Missing PHOTOCOLLECT_DEEPLINK_SECRET');
}

$payload = [
    'customer_no' => '12345678',
    'site_code' => 'demo',
    'customer_to' => 'user@example.com',
    'expiry_date' => (new DateTimeImmutable(
        '+90 days',
        new DateTimeZone('UTC')
    ))->format('Y-m-d'),
    'expiry_timezone' => 'UTC',
    'salt' => bin2hex(random_bytes(16)),
    'config' => [
        'collect_redirect_uri' => 'https://example.com/thank-you',
    ],
];

// Sign and send this exact JSON string.
$payloadJson = json_encode(
    $payload,
    JSON_UNESCAPED_SLASHES | JSON_THROW_ON_ERROR
);
$payloadEncoded = base64_encode($payloadJson);
$sig = hash_hmac('sha256', $payloadJson, $secret); // lowercase HEX

$query = http_build_query([
    'payload' => $payloadEncoded,
    'sig' => $sig,
    'locale' => 'en_US',
], '', '&', PHP_QUERY_RFC3986);

$url = 'https://demo.photocollect.io/collect/new?' . $query;
echo $url;

const crypto = require('node:crypto');

const secret = process.env.PHOTOCOLLECT_DEEPLINK_SECRET;
if (!secret) throw new Error('Missing PHOTOCOLLECT_DEEPLINK_SECRET');

const expiryDate = new Date();
expiryDate.setUTCDate(expiryDate.getUTCDate() + 90);

const payload = {
  customer_no: '12345678',
  site_code: 'demo',
  customer_to: 'user@example.com',
  expiry_date: expiryDate.toISOString().slice(0, 10),
  expiry_timezone: 'UTC',
  salt: crypto.randomBytes(16).toString('hex'),
  config: {
    collect_redirect_uri: 'https://example.com/thank-you',
  },
};

// Sign and send this exact JSON string.
const payloadJson = JSON.stringify(payload);
const payloadEncoded = Buffer.from(payloadJson, 'utf8').toString('base64');
const sig = crypto
  .createHmac('sha256', secret)
  .update(payloadJson, 'utf8')
  .digest('hex');

const query = new URLSearchParams({
  payload: payloadEncoded,
  sig,
  locale: 'en_US',
});

const url = `https://demo.photocollect.io/collect/new?${query}`;
console.log(url);

import base64
import hashlib
import hmac
import json
import os
import secrets
from datetime import datetime, timedelta, timezone
from urllib.parse import urlencode

secret = os.environ.get("PHOTOCOLLECT_DEEPLINK_SECRET", "")
if not secret:
    raise RuntimeError("Missing PHOTOCOLLECT_DEEPLINK_SECRET")

secret_bytes = secret.encode("utf-8")
payload = {
    "customer_no": "12345678",
    "site_code": "demo",
    "customer_to": "user@example.com",
    "expiry_date": (
        datetime.now(timezone.utc).date() + timedelta(days=90)
    ).isoformat(),
    "expiry_timezone": "UTC",
    "salt": secrets.token_hex(16),
    "config": {
        "collect_redirect_uri": "https://example.com/thank-you",
    },
}

# Compact JSON makes the exact signed bytes explicit.
payload_json = json.dumps(payload, separators=(",", ":"), ensure_ascii=False)
payload_bytes = payload_json.encode("utf-8")
payload_encoded = base64.b64encode(payload_bytes).decode("ascii")
sig = hmac.new(
    secret_bytes,
    payload_bytes,
    hashlib.sha256,
).hexdigest()

query = urlencode({
    "payload": payload_encoded,
    "sig": sig,
    "locale": "en_US",
})
url = "https://demo.photocollect.io/collect/new?" + query
print(url)

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Text.Json;

var secret = Environment.GetEnvironmentVariable(
    "PHOTOCOLLECT_DEEPLINK_SECRET"
);
if (string.IsNullOrEmpty(secret))
{
    throw new InvalidOperationException(
        "Missing PHOTOCOLLECT_DEEPLINK_SECRET"
    );
}

var payload = new Dictionary<string, object>
{
    ["customer_no"] = "12345678",
    ["site_code"] = "demo",
    ["customer_to"] = "user@example.com",
    ["expiry_date"] = DateTime.UtcNow.AddDays(90).ToString(
        "yyyy-MM-dd", CultureInfo.InvariantCulture
    ),
    ["expiry_timezone"] = "UTC",
    ["salt"] = Convert.ToHexString(
        RandomNumberGenerator.GetBytes(16)
    ).ToLowerInvariant(),
    ["config"] = new Dictionary<string, object>
    {
        ["collect_redirect_uri"] = "https://example.com/thank-you"
    }
};

// Sign and send this exact JSON string.
var payloadJson = JsonSerializer.Serialize(payload);
var payloadEncoded = Convert.ToBase64String(
    Encoding.UTF8.GetBytes(payloadJson)
);

using var hmac = new HMACSHA256(Encoding.UTF8.GetBytes(secret));
var signature = hmac.ComputeHash(Encoding.UTF8.GetBytes(payloadJson));
var sig = Convert.ToHexString(signature).ToLowerInvariant();

var query = new Dictionary<string, string>
{
    ["payload"] = payloadEncoded,
    ["sig"] = sig,
    ["locale"] = "en_US"
};
var encodedQuery = string.Join("&", query.Select(pair =>
    $"{Uri.EscapeDataString(pair.Key)}={Uri.EscapeDataString(pair.Value)}"));

var url = $"https://demo.photocollect.io/collect/new?{encodedQuery}";
Console.WriteLine(url);

import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;
import javax.crypto.Mac;
import javax.crypto.spec.SecretKeySpec;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.security.SecureRandom;
import java.time.LocalDate;
import java.time.ZoneOffset;
import java.util.Base64;

public class PhotoCollectDeeplink {
    public static void main(String[] args) throws Exception {
        String secret = System.getenv("PHOTOCOLLECT_DEEPLINK_SECRET");
        if (secret == null || secret.isEmpty()) {
            throw new IllegalStateException(
                "Missing PHOTOCOLLECT_DEEPLINK_SECRET"
            );
        }

        ObjectMapper mapper = new ObjectMapper();
        ObjectNode payload = mapper.createObjectNode();
        payload.put("customer_no", "12345678");
        payload.put("site_code", "demo");
        payload.put("customer_to", "user@example.com");
        payload.put(
            "expiry_date",
            LocalDate.now(ZoneOffset.UTC).plusDays(90).toString()
        );
        payload.put("expiry_timezone", "UTC");
        byte[] salt = new byte[16];
        new SecureRandom().nextBytes(salt);
        payload.put("salt", toLowerHex(salt));
        payload.putObject("config").put(
            "collect_redirect_uri", "https://example.com/thank-you"
        );

        // Sign and send this exact JSON string.
        String payloadJson = mapper.writeValueAsString(payload);
        String encoded = Base64.getEncoder().encodeToString(
            payloadJson.getBytes(StandardCharsets.UTF_8)
        );
        String sig = hmacSha256Hex(payloadJson, secret);

        String query = "payload=" + encode(encoded)
            + "&sig=" + encode(sig)
            + "&locale=" + encode("en_US");
        System.out.println(
            "https://demo.photocollect.io/collect/new?" + query
        );
    }

    private static String encode(String value) {
        return URLEncoder.encode(value, StandardCharsets.UTF_8);
    }

    private static String hmacSha256Hex(String data, String secret)
            throws Exception {
        Mac mac = Mac.getInstance("HmacSHA256");
        mac.init(new SecretKeySpec(
            secret.getBytes(StandardCharsets.UTF_8), "HmacSHA256"
        ));
        byte[] hash = mac.doFinal(data.getBytes(StandardCharsets.UTF_8));
        return toLowerHex(hash);
    }

    private static String toLowerHex(byte[] bytes) {
        char[] digits = "0123456789abcdef".toCharArray();
        char[] result = new char[bytes.length * 2];
        for (int i = 0; i < bytes.length; i++) {
            int value = bytes[i] & 0xff;
            result[i * 2] = digits[value >>> 4];
            result[i * 2 + 1] = digits[value & 0x0f];
        }
        return new String(result);
    }
}

Add the required crates with:

cargo add serde_json base64 chrono hmac sha2 hex rand url

use base64::{engine::general_purpose::STANDARD, Engine as _};
use chrono::{Duration, Utc};
use hmac::{Hmac, Mac};
use serde_json::json;
use sha2::Sha256;
use std::env;
use url::Url;

type HmacSha256 = Hmac<Sha256>;

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let secret = env::var("PHOTOCOLLECT_DEEPLINK_SECRET")?;
    if secret.is_empty() {
        return Err("Missing PHOTOCOLLECT_DEEPLINK_SECRET".into());
    }

    let payload = json!({
        "customer_no": "12345678",
        "site_code": "demo",
        "customer_to": "user@example.com",
        "expiry_date": (Utc::now().date_naive() + Duration::days(90))
            .to_string(),
        "expiry_timezone": "UTC",
        "salt": hex::encode(rand::random::<[u8; 16]>()),
        "config": {
            "collect_redirect_uri": "https://example.com/thank-you"
        }
    });

    // Sign and send this exact JSON string.
    let payload_json = serde_json::to_string(&payload)?;
    let payload_encoded = STANDARD.encode(payload_json.as_bytes());

    let mut mac = HmacSha256::new_from_slice(secret.as_bytes())?;
    mac.update(payload_json.as_bytes());
    let sig = hex::encode(mac.finalize().into_bytes());

    let mut url = Url::parse(
        "https://demo.photocollect.io/collect/new"
    )?;
    url.query_pairs_mut()
        .append_pair("payload", &payload_encoded)
        .append_pair("sig", &sig)
        .append_pair("locale", "en_US");

    println!("{url}");
    Ok(())
}

Store PHOTOCOLLECT_DEEPLINK_SECRET in Project Settings → Script Properties before using this custom function. Only use this approach when every spreadsheet editor is trusted with the secret: editors can open the bound Apps Script project. For broader distribution, call a server-side signing endpoint instead.

The salt cell must contain a stable, unique value for this link. Do not pass a volatile formula such as NOW() or RAND().

const PHOTOCOLLECT_BASE_URL =
  'https://demo.photocollect.io/collect/new';

/**
 * @customfunction
 * @param {string} customerNo Customer number
 * @param {string} siteCode Photo Collect site code
 * @param {string} customerTo Recipient email or phone
 * @param {Date|string} expiryDate Expiry date
 * @param {string|number} salt Unique salt
 * @param {string} locale Upload locale
 * @return {string} Signed Photo Collect Deeplink
 */
function PHOTOCOLLECT_LINK(
  customerNo, siteCode, customerTo, expiryDate, salt, locale
) {
  const secret = PropertiesService.getScriptProperties()
    .getProperty('PHOTOCOLLECT_DEEPLINK_SECRET');
  if (!secret) throw new Error('Missing Photo Collect Deeplink secret');

  const payload = {
    site_code: requiredString_(siteCode, 'siteCode'),
    expiry_date: formatSheetDate_(expiryDate),
    salt: requiredString_(salt, 'salt'),
  };
  const normalizedCustomerNo = optionalString_(customerNo);
  const normalizedCustomerTo = optionalString_(customerTo);
  if (normalizedCustomerNo) payload.customer_no = normalizedCustomerNo;
  if (normalizedCustomerTo) payload.customer_to = normalizedCustomerTo;

  const payloadJson = JSON.stringify(payload);
  const payloadEncoded = Utilities.base64Encode(
    payloadJson, Utilities.Charset.UTF_8
  );
  const signatureBytes = Utilities.computeHmacSha256Signature(
    payloadJson, secret, Utilities.Charset.UTF_8
  );
  const sig = bytesToHex_(signatureBytes);

  const query = [
    'payload=' + encodeURIComponent(payloadEncoded),
    'sig=' + encodeURIComponent(sig),
    'locale=' + encodeURIComponent(optionalString_(locale) || 'en_US'),
  ].join('&');
  return PHOTOCOLLECT_BASE_URL + '?' + query;
}

function bytesToHex_(bytes) {
  return bytes.map(function (byte) {
    const value = byte < 0 ? byte + 256 : byte;
    return ('0' + value.toString(16)).slice(-2);
  }).join('');
}

function formatSheetDate_(value) {
  if (Object.prototype.toString.call(value) === '[object Date]') {
    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    const timeZone = spreadsheet
      ? spreadsheet.getSpreadsheetTimeZone()
      : Session.getScriptTimeZone();
    return Utilities.formatDate(
      value, timeZone, 'yyyy-MM-dd'
    );
  }
  const formatted = optionalString_(value);
  if (!/^\d{4}-\d{2}-\d{2}$/.test(formatted)) {
    throw new Error('expiryDate must be a date or yyyy-MM-dd string');
  }
  return formatted;
}

function optionalString_(value) {
  return value == null ? '' : String(value).trim();
}

function requiredString_(value, name) {
  const normalized = optionalString_(value);
  if (!normalized) throw new Error(name + ' is required');
  return normalized;
}

Caution: This example hardcodes the Deeplink secret for simplicity. Anyone with access to the add-in code can recover it. In production, keep the secret server-side and have the add-in call an authenticated signing service.

Pass the expiry as an ISO date string to avoid ambiguity between Excel's 1900 and 1904 date systems, for example =PHOTOCOLLECT_LINK(A2,B2,C2,TEXT(D2,"yyyy-mm-dd"),E2,"en_US").

const PHOTOCOLLECT_SECRET = "<DEEPLINK_SECRET>";
const PHOTOCOLLECT_BASE_URL =
  "https://demo.photocollect.io/collect/new";

/**
 * @customfunction
 * @param customerNo Customer number
 * @param siteCode Photo Collect site code
 * @param customerTo Recipient email or phone
 * @param expiryDate Expiry date in yyyy-MM-dd format
 * @param salt Unique salt
 * @param locale Upload locale
 */
export async function PHOTOCOLLECT_LINK(
  customerNo: string,
  siteCode: string,
  customerTo: string,
  expiryDate: string,
  salt: string | number,
  locale: string = "en_US"
): Promise<string> {
  const payload: Record<string, string> = {
    site_code: requiredString(siteCode, "siteCode"),
    expiry_date: formatExcelDate(expiryDate),
    salt: requiredString(salt, "salt"),
  };
  const normalizedCustomerNo = optionalString(customerNo);
  const normalizedCustomerTo = optionalString(customerTo);
  if (normalizedCustomerNo) payload.customer_no = normalizedCustomerNo;
  if (normalizedCustomerTo) payload.customer_to = normalizedCustomerTo;

  // Sign and send this exact JSON string.
  const payloadJson = JSON.stringify(payload);
  const payloadEncoded = base64EncodeUtf8(payloadJson);
  const sig = await hmacSha256Hex(
    payloadJson, PHOTOCOLLECT_SECRET
  );
  const query = new URLSearchParams({
    payload: payloadEncoded,
    sig,
    locale: optionalString(locale) || "en_US",
  });
  return `${PHOTOCOLLECT_BASE_URL}?${query}`;
}

async function hmacSha256Hex(
  data: string, secret: string
): Promise<string> {
  const encoder = new TextEncoder();
  const key = await crypto.subtle.importKey(
    "raw",
    encoder.encode(secret),
    { name: "HMAC", hash: "SHA-256" },
    false,
    ["sign"]
  );
  const signature = await crypto.subtle.sign(
    "HMAC", key, encoder.encode(data)
  );
  return Array.from(new Uint8Array(signature))
    .map((byte) => byte.toString(16).padStart(2, "0"))
    .join("");
}

function base64EncodeUtf8(value: string): string {
  const bytes = new TextEncoder().encode(value);
  let binary = "";
  bytes.forEach((byte) => binary += String.fromCharCode(byte));
  return btoa(binary);
}

function formatExcelDate(value: string): string {
  const formatted = optionalString(value);
  if (!/^\d{4}-\d{2}-\d{2}$/.test(formatted)) {
    throw new Error("expiryDate must use yyyy-MM-dd");
  }
  return formatted;
}

function optionalString(value: unknown): string {
  return value == null ? "" : String(value).trim();
}

function requiredString(value: unknown, name: string): string {
  const normalized = optionalString(value);
  if (!normalized) throw new Error(`${name} is required`);
  return normalized;
}

COBOL does not define portable cryptography, Base64, URL encoding, secure random generation, or secret storage. Bind the named routines below to vetted implementations for your runtime. The length arguments are significant: do not sign or encode the trailing spaces in fixed-width fields.

       IDENTIFICATION DIVISION.
       PROGRAM-ID. BUILD-DEEPLINK.

       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  WS-SECRET       PIC X(128).
       01  WS-SECRET-LEN   PIC 9(4) COMP.
       01  WS-LOCALE       PIC X(10) VALUE "en_US".
       01  WS-BASE-URL     PIC X(100) VALUE
           "https://demo.photocollect.io/collect/new".
       01  WS-SALT         PIC X(32).
       01  WS-SALT-LEN     PIC 9(4) COMP.
       01  WS-EXPIRY-DATE  PIC X(10).
       01  WS-PAYLOAD-JSON PIC X(500).
       01  WS-PAYLOAD-PTR  PIC 9(4) COMP VALUE 1.
       01  WS-PAYLOAD-LEN  PIC 9(4) COMP.
       01  WS-PAYLOAD-B64  PIC X(800).
       01  WS-PAYLOAD-B64-LEN PIC 9(4) COMP.
       01  WS-PAYLOAD-ESC  PIC X(2400).
       01  WS-PAYLOAD-ESC-LEN PIC 9(4) COMP.
       01  WS-SIG-HEX      PIC X(64).
       01  WS-URL          PIC X(3000).

       PROCEDURE DIVISION.
           *> Your IT infrastructure may be from the last century,
           *> but your photo capture process does not have to be.

           *> Load from protected configuration; never hardcode the secret.
           CALL "LOAD-DEEPLINK-SECRET" USING
               WS-SECRET WS-SECRET-LEN

           *> Produce 16 random bytes as 32 lowercase hex characters.
           CALL "SECURE-RANDOM-HEX" USING
               WS-SALT WS-SALT-LEN

           *> Produce the date 90 days from today in YYYY-MM-DD format.
           CALL "GET-EXPIRY-DATE" USING WS-EXPIRY-DATE

           STRING
               '{"customer_no":"12345678",' DELIMITED BY SIZE
               '"site_code":"demo",' DELIMITED BY SIZE
               '"customer_to":"user@example.com",' DELIMITED BY SIZE
               '"expiry_date":"' DELIMITED BY SIZE
               WS-EXPIRY-DATE DELIMITED BY SIZE
               '","salt":"' DELIMITED BY SIZE
               WS-SALT(1:WS-SALT-LEN) DELIMITED BY SIZE
               '"}' DELIMITED BY SIZE
               INTO WS-PAYLOAD-JSON
               WITH POINTER WS-PAYLOAD-PTR
           END-STRING
           COMPUTE WS-PAYLOAD-LEN = WS-PAYLOAD-PTR - 1

           CALL "BASE64-ENCODE" USING
               WS-PAYLOAD-JSON WS-PAYLOAD-LEN
               WS-PAYLOAD-B64 WS-PAYLOAD-B64-LEN

           *> HMAC-SHA256 over raw JSON; return lowercase HEX.
           CALL "HMAC-SHA256-HEX" USING
               WS-SECRET WS-SECRET-LEN
               WS-PAYLOAD-JSON WS-PAYLOAD-LEN
               WS-SIG-HEX

           *> Standard Base64 contains +, /, and =; escape it for the URL.
           CALL "URL-ENCODE" USING
               WS-PAYLOAD-B64 WS-PAYLOAD-B64-LEN
               WS-PAYLOAD-ESC WS-PAYLOAD-ESC-LEN

           STRING
               FUNCTION TRIM(WS-BASE-URL) DELIMITED BY SIZE
               "?payload=" DELIMITED BY SIZE
               WS-PAYLOAD-ESC(1:WS-PAYLOAD-ESC-LEN)
                   DELIMITED BY SIZE
               "&sig=" DELIMITED BY SIZE
               WS-SIG-HEX DELIMITED BY SIZE
               "&locale=" DELIMITED BY SIZE
               FUNCTION TRIM(WS-LOCALE) DELIMITED BY SIZE
               INTO WS-URL
           END-STRING

           DISPLAY FUNCTION TRIM(WS-URL)
           GOBACK.