Integration recipe

Publish to pixPLace from Google Sheets with Apps Script

An Apps Script reads rows from a Google Sheet, uploads each image by URL and publishes a post, and writes the post link back.

OpenAPI spec Get an API key

Who it is for

Content managers who plan posts in a spreadsheet and want to publish them without leaving it.

What it automates

Publish every row that has no status yet: upload its image by URL, create the post and write the post address into the sheet.

Steps

  1. Make a sheet with the columns A id, B title, C image URL and D status. The id must never change, because it goes into the Idempotency-Key.
  2. Open Extensions, Apps Script, paste the code below and save the key as the script property PIXPLACE_API_KEY (Project Settings, Script properties).
  3. Run publishPending once and accept the permissions. Add a time-driven trigger if it should run by itself.
  4. A row with an error text in D is skipped next time. Clear the cell to try again: the same id gives the same Idempotency-Key, so nothing is published twice.
  5. When you create the key, tick the `publish` scope. A key without it gets 403 insufficient_scope on these calls.

Example

Placeholders: ppk_live_XXXXXXXX is your key. The ids and the image address are samples. The API address is always https://pixplace.space/api/v1.

Code.gs
const BASE = "https://pixplace.space/api/v1";

function publishPending() {
  const key = PropertiesService.getScriptProperties().getProperty("PIXPLACE_API_KEY");
  const sheet = SpreadsheetApp.getActiveSheet();
  const rows = sheet.getDataRange().getValues();
  for (let i = 1; i < rows.length; i++) {
    const [id, title, imageUrl, status] = rows[i];
    if (status) continue;
    try {
      const upload = call_("/uploads/from-url", { url: imageUrl }, key, "sheet-upload-" + id);
      const post = call_("/posts", { title: title, attachments: [{ url: upload.data.publicUrl, type: upload.data.type }] }, key, "sheet-post-" + id);
      sheet.getRange(i + 1, 4).setValue(post.data.url);
    } catch (e) {
      sheet.getRange(i + 1, 4).setValue("ERROR: " + e.message);
      break;
    }
  }
}

function call_(path, body, key, idempotencyKey) {
  const res = UrlFetchApp.fetch(BASE + path, {
    method: "post",
    contentType: "application/json",
    headers: { Authorization: "Bearer " + key, "Idempotency-Key": idempotencyKey },
    payload: JSON.stringify(body),
    muteHttpExceptions: true,
  });
  const code = res.getResponseCode();
  if (code === 429 || code === 503) throw new Error("wait " + res.getHeaders()["Retry-After"] + " s and run again");
  if (code >= 300) throw new Error(code + " " + res.getContentText());
  return JSON.parse(res.getContentText());
}

Common errors

StatusCauseWhat to do
400A cell is empty or the image address is not an https URL, so the title or the url is invalid. The message names the field.Fix the cell, clear the status in column D and run again. Nothing was written for that row.
401The API key is missing, malformed, revoked or sent in the wrong header.The script property is missing or has another name. Open Project Settings and check that PIXPLACE_API_KEY exists. The header must read Authorization: Bearer followed by your ppk_live_ key. Check the key in Settings → Developer.
403The key lacks a scope the call needs: the answer is 403 with error.code insufficient_scope and the missing scope in error.required.Create a new key in Settings → Developer with the `publish` scope ticked and use it instead; repeating the call with the same key fails the same way.
429A per-minute or per-day limit was reached. The Retry-After header and the retryAfter field of the body say how many seconds to wait.Wait that long and repeat with the SAME Idempotency-Key. The script stops at the first limited row and writes the wait time into column D. Clear the cell and run it later, the ids keep the retries safe.
502The server could not download the source URL: it answered 4xx or 5xx, the host does not resolve or the response was compressed.Open the source URL in a private browser window. It must be public and answer 200 without a login.

The full list of statuses, the retry rules and the limits are in Errors and retries.

Links

Other tools