Notion script error handling

2025-10-29 16:30 UTC gpt-5-thinking Open in ChatGPT ↗

You

this function of my google apps script Library causes an error, probably related to the results variable being empty after a 400 error. make sure it doesnt crash but givers a very verbose and helpful message that allows me to pinpoint the issue with Notion

function getNotionDatabaseItems(_databaseId){ //This is setup to work with pagination, useful for databases with more than 100 pages which is the limit of Notion for each request // Set the initial values for the pagination parameters var cursor = undefined; // Set an empty array to store the results var results = [];

// Loop until there are no more pages to retrieve while(true) { // Call the getNotionDatabase function to retrieve the current page var page = getNotionDatabase(_databaseId, cursor); // Add the results from the current page to the results array results = results.concat(page.results); // Update the cursor to the next page by retrieving the next_cursor parameter provided by the Notion API cursor = page.next_cursor; // If the has_more flag is false, there are no more pages to retrieve if (!page.has_more) { break; } } // Return the results return results; }

ChatGPT

[Unsupported Content]

ChatGPT

[Unsupported Content]

ChatGPT

Gotcha—here’s a drop-in, defensive version that won’t crash when Notion returns a 4xx/5xx (or any malformed payload). It validates every response, handles pagination safely, and produces a very verbose, actionable debug message so you can pinpoint what’s wrong with your Notion setup.

You’ll get a structured return like:

{ ok: true|false, results: [...], debug: "long multi-line message" }

No throwing—so your script won’t die. If something’s wrong, ok:false, results:[], and debug explains exactly what to check.

/**
 * Robust, non-throwing wrapper to fetch all items from a Notion database.
 * - Never throws; returns { ok, results, debug }.
 * - Adds strong validation and pagination safeguards.
 * - Builds a verbose, actionable debug string on any failure.
 *
 * @param {string} _databaseId
 * @param {object} [opts]
 * @param {number} [opts.maxPages=1000]   Safety cap for runaway pagination.
 * @param {boolean} [opts.log=true]       Also log the debug message to Stackdriver.
 */
function getNotionDatabaseItems(_databaseId, opts) {
  opts = opts || {};
  var maxPages = typeof opts.maxPages === 'number' ? opts.maxPages : 1000;
  var doLog = (opts.log !== false);

  var cursor = undefined;
  var results = [];
  var pageCount = 0;

  // Helper: safe preview of large/unknown objects
  function preview(obj, max) {
    max = max || 1000;
    try {
      var s = typeof obj === 'string' ? obj : JSON.stringify(obj);
      if (s && s.length > max) return s.slice(0, max) + '…(truncated)';
      return s;
    } catch (e) { return '[unserializable object]'; }
  }

  // Helper: assemble a very detailed debug message
  function buildDebug(title, context) {
    var lines = [];
    lines.push('getNotionDatabaseItems: ' + title);
    lines.push('Timestamp: ' + new Date().toISOString());
    lines.push('Database ID: ' + _databaseId);
    if (context) {
      if ('cursor' in context) lines.push('Cursor: ' + preview(context.cursor));
      if ('pageRaw' in context) lines.push('Last page raw (preview): ' + preview(context.pageRaw, 4000));
      if ('status' in context) lines.push('HTTP status: ' + context.status);
      if ('error' in context) lines.push('Caught error: ' + context.error);
      if ('notionCode' in context) lines.push('Notion error code: ' + context.notionCode);
      if ('notionMessage' in context) lines.push('Notion error message: ' + context.notionMessage);
      if ('pageCount' in context) lines.push('Pages retrieved so far: ' + context.pageCount);
      if ('resultsCount' in context) lines.push('Items retrieved so far: ' + context.resultsCount);
    }
    // Extremely specific troubleshooting checklist for Notion
    lines.push('');
    lines.push('Troubleshooting checklist:');
    lines.push('- Verify the database ID is correct (from the database URL).');
    lines.push('- Ensure the integration has been ADDED to this database (Share → Invite your integration).');
    lines.push('- Confirm the Notion token used in getNotionDatabase(...) is valid and not expired/revoked.');
    lines.push('- Make sure the "Notion-Version" header matches a supported date and is consistent everywhere.');
    lines.push('- If you pass filter/sorts in getNotionDatabase(...), validate property names and types match the database schema exactly.');
    lines.push('- Check for 400/404: often invalid database ID, bad filter JSON, or missing integration access.');
    lines.push('- Check for 429: respect rate limits or add retry/backoff.');
    lines.push('- Check for 403: integration lacks permission to read this database.');
    lines.push('- If this started after a schema change, re-check property keys used in filters/queries.');
    return lines.join('\n');
  }

  // Main loop with strong guards
  while (true) {
    var page;
    try {
      page = getNotionDatabase(_databaseId, cursor); // your existing lower-level call
    } catch (e) {
      var debug = buildDebug('Underlying getNotionDatabase(...) threw an exception.', {
        cursor: cursor, error: String(e), pageCount: pageCount, resultsCount: results.length
      });
      if (doLog) console.error(debug);
      return { ok: false, results: [], debug: debug };
    }

    // Validate we got an object
    if (!page || typeof page !== 'object') {
      var debugInvalid = buildDebug('Invalid response: expected an object from Notion, got ' + typeof page + '.', {
        cursor: cursor, pageRaw: page, pageCount: pageCount, resultsCount: results.length
      });
      if (doLog) console.error(debugInvalid);
      return { ok: false, results: [], debug: debugInvalid };
    }

    // If the lower-level call bubbles up raw Notion errors, catch them here
    // Common shapes: {object:"error", status, code, message}
    if (page.object === 'error' || (page.status && page.status >= 400)) {
      var debugErr = buildDebug('Notion API returned an error object.', {
        cursor: cursor,
        status: page.status,
        notionCode: page.code,
        notionMessage: page.message,
        pageRaw: page,
        pageCount: pageCount,
        resultsCount: results.length
      });
      if (doLog) console.error(debugErr);
      return { ok: false, results: [], debug: debugErr };
    }

    // Validate pagination shape
    if (!('results' in page)) {
      var debugNoResults = buildDebug('Response missing `results` field.', {
        cursor: cursor, pageRaw: page, pageCount: pageCount, resultsCount: results.length
      });
      if (doLog) console.error(debugNoResults);
      return { ok: false, results: [], debug: debugNoResults };
    }
    if (!Array.isArray(page.results)) {
      var debugBadResults = buildDebug('`results` is not an array.', {
        cursor: cursor, pageRaw: page, pageCount: pageCount, resultsCount: results.length
      });
      if (doLog) console.error(debugBadResults);
      return { ok: false, results: [], debug: debugBadResults };
    }

    // Accumulate items
    results = results.concat(page.results);
    pageCount++;

    // Safeguard on runaway pagination
    if (pageCount > maxPages) {
      var debugMax = buildDebug('Pagination exceeded safety cap (maxPages=' + maxPages + ').', {
        cursor: cursor, pageRaw: page, pageCount: pageCount, resultsCount: results.length
      });
      if (doLog) console.error(debugMax);
      return { ok: false, results: results, debug: debugMax };
    }

    // Advance cursor; stop if no more pages
    var hasMore = !!page.has_more;
    cursor = page.next_cursor || null;

    if (!hasMore || !cursor) {
      // Success path
      var successDebug = 'Fetched ' + results.length + ' items in ' + pageCount + ' page(s).';
      if (doLog) console.log('getNotionDatabaseItems: ' + successDebug);
      return { ok: true, results: results, debug: successDebug };
    }
  }
}

How to use it

var res = getNotionDatabaseItems('YOUR_DATABASE_ID_HERE');
if (!res.ok) {
  // Won’t crash—inspect the detailed message
  Logger.log(res.debug);
  // Optionally surface to user/UI
  // SpreadsheetApp.getUi().alert(res.debug);
} else {
  var items = res.results; // your array of pages
  // proceed...
}

Why this fixes your crash

• Your original code assumes page.results always exists. On a 400, Notion returns an error object with no results, causing .concat(page.results) to blow up.
• This version checks for error shapes and missing fields before touching them, then returns { ok:false, debug:... } instead of throwing—so the rest of your script keeps control and you get a pinpointed explanation of what to fix in Notion.

You

Ok now I fixed my library. How do I updated it on Google Apps Script?

ChatGPT

[Unsupported Content]

ChatGPT

[Unsupported Content]

ChatGPT

Here’s the quick path, depending on whether you own the library or you’re just using it in another script.

If you OWN the library

1. Open the library project in Apps Script.
2. File → Manage versions… → Save New Version.
   • Give it a clear description (e.g., “Fix 400 handling + verbose debug”).
3. (Optional but handy) Project Settings → Enable development mode for libraries if you want consumers (you) to use the latest code without versioning while testing.
4. Note the Script ID (Project Settings) in case you need to share/reference it.

If you USE the library in another project

1. Open the consumer project (the one that imports the library).
2. In the left sidebar, click Libraries (puzzle-piece icon) → find your library.
3. In the Version dropdown, select the new version number you just created → Save.
   • If you don’t see it, click Refresh (⟳) next to the dropdown.
4. Run any function; you may be prompted to re-authorize if scopes changed.

Tips / gotchas

• “Save” isn’t a version. You must use Manage versions → Save New Version; otherwise the consumer project can’t pick it.
• Can’t see new version? Refresh the consumer project, re-open Libraries, or temporarily Remove and Add the library again via Script ID.
• Changed scopes? Consumers will get a new auth prompt the next run.
• Development mode: In the consumer project’s Project Settings, you can enable “Use latest code” (development mode) for libraries you can edit. Great for testing; turn it off and pin a numbered version for production.
• clasp (if you use it):
  • clasp push (push code)
  • clasp version "Fix 400 handling + verbose debug" (creates a numbered version)
  • Then update the library version in the consumer project as above.

That’s it—create a new version in the library, then select that version in projects that depend on it.

You

the original error is this. However i didnt get it with a different item from the same database:

getNotionDatabase - Request failed. Expected 200, got 400: {“object”:“error”,“status”:400,“code”:“validation_error”,“message”:“Provided ID 29b6716c-a1e8-80c9-8d4a-e80eecd45e5e is a block, not a database. Use the retrieve block API instead”,“request_id”:“2b2aa593-da01-493e-914e-05c5a01addf3”}

ChatGPT

[Unsupported Content]

ChatGPT

[Unsupported Content]

ChatGPT

That 400 tells you exactly what’s wrong:

“Provided ID … is a block, not a database. Use the retrieve block API instead.”

What’s happening

• You’re calling /databases/{database_id}/query (or your wrapper around it) with an ID that belongs to a block (often a database view block or an inline/child database block) rather than the database’s ID.
• With a different item from the same database it worked because in that run you happened to pass the actual database_id.

Common ways this ID mix-up happens

• Copying the URL of a view (ends with ?v=VIEW_BLOCK_ID) or a block link. That VIEW_BLOCK_ID is not the database_id.
• Using the ID of a page in the database (a row). That’s a page_id, not the database_id.
• Using the ID of an inline database block (child_database). It’s still a block ID; querying the database requires the database_id.

---

How to get the correct database_id

Pick any of these—use whichever is easiest:

1. From the Notion UI (most reliable)

   • Open the database in Notion and choose Open as full page.
   • Click … (More) → Copy link.
   • The URL contains the database ID as a 32-char hex string (with or without hyphens). Example:
     https://www.notion.so/Your-DB-Name-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa → aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa is the database_id.
     (If you see ?v=XXXXXXXX… at the end, that trailing part is a view id—ignore it.)

2. From a page (row) you know is in the target database

   • Call Retrieve page for that page (row) and read parent.database_id.
   • Use that database_id with /databases/{id}/query.

3. Via API Search

   • Use /v1/search with filter: { "property": "object", "value": "database" }, then pick your DB from the results and take its id.

---

Quick code guard (so you never chase this again)

Add a preflight “retrieve database” check before you paginate/query. If the ID is wrong, you’ll get a clear, immediate message and avoid the crash.

/**
 * Ensure the given ID is a database_id. Returns { ok, database, debug }.
 */
function assertDatabaseId(notionToken, id) {
  var url = 'https://api.notion.com/v1/databases/' + id;
  var headers = {
    'Authorization': 'Bearer ' + notionToken,
    'Notion-Version': '2022-06-28', // use your version
    'Content-Type': 'application/json'
  };
  try {
    var res = UrlFetchApp.fetch(url, { method: 'get', muteHttpExceptions: true, headers: headers });
    var status = res.getResponseCode();
    var body = res.getContentText();
    var json = JSON.parse(body);

    if (status === 200 && json && json.object === 'database') {
      return { ok: true, database: json, debug: 'Validated database_id: ' + id };
    }

    // Not OK: bubble up a very specific message for block/view mixups
    var code = json && json.code;
    var msg  = json && json.message;
    var dbg = [
      'Notion rejected this ID as a database_id.',
      'Status: ' + status,
      'Code: ' + code,
      'Message: ' + msg,
      'Hint: If the ID came from a URL with ?v=..., that tail is a VIEW BLOCK ID. Open the database as full page and copy its link.',
      'ID you passed: ' + id
    ].join('\n');

    return { ok: false, database: null, debug: dbg };

  } catch (e) {
    return { ok: false, database: null, debug: 'Exception during database verify: ' + e };
  }
}

Use it in your fetcher:

function getNotionDatabaseItemsSafe(notionToken, databaseId) {
  var pre = assertDatabaseId(notionToken, databaseId);
  if (!pre.ok) {
    // Surface a crystal-clear explanation and stop early
    throw new Error(pre.debug);
  }
  // …then do your normal /databases/{id}/query pagination
}

---

Sanity checklist

• ✅ The ID I pass to /databases/{id}/query comes from Copy link on the database as a full page, not from a view or a row.
• ✅ If I only have a row (page) ID, I first retrieve page and use parent.database_id.
• ✅ My library’s error handling detects code:"validation_error" + “is a block, not a database” and prints a fix-it hint about view/block vs database IDs.

If you paste the exact URL you used (redact the workspace part), I can point to the precise substring that is the correct database_id.

You

This is where the error originates.

function getNotionInvoiceTables(databaseid,index){ //pass a database id and an index - the index is needed to iterate this whole function for every database item inside the createInvoiceDocument function Logger.log(databaseid);

var data = ULC_NotionAPI.notionData([],databaseid);//‘055ac6e934044befa59a8db1c34a4e86’) //get Income database entries - for testing - database id will be passed from the functions arguments //Logger.log(data) var mypageid = ULC_NotionAPI.getNotionPageContent(data[index][“id”]) //this will be iterated in a loop - 0 will become i

var scopeID = mypageid.results[0][“id”]; var feesID = mypageid.results[1][“id”];

var scopeDB = ULC_NotionAPI.notionData([],scopeID);//[0][“properties”][“Name”][“title”][0][“plain_text”]; var feesDB = ULC_NotionAPI.notionData([],feesID); var databases = [scopeDB,feesDB]; var myvalues = []; var propertynames = []; var iterations = [];

for (var d=0; d<databases.length; d++) { var cur_DB = databases[d]; var mycurvalues = []; var curpropertynames = Object.keys(cur_DB[0][“properties”]); for (var i = 0; i < cur_DB.length; i++) { var properties = cur_DB[i][“properties”]; for (p in properties) { var myproperty = properties[p] var type = myproperty[“type”]; mycurvalues.push(ULC_NotionAPI.getNotionProperties(myproperty, type)); } }

propertynames.push(curpropertynames)
myvalues.push(ULC_Sheets.splitToChunks(mycurvalues,cur_DB.length))
iterations.push(cur_DB.length)

}

This line causes the 400 “it’s a block not a db” error var scopeDB = ULC_NotionAPI.notionData([],scopeID);//[0][“properties”][“Name”][“title”][0][“plain_text”];

ChatGPT

[Unsupported Content]

ChatGPT

[Unsupported Content]

ChatGPT

[Unsupported Content]

ChatGPT

[Unsupported Content]

ChatGPT

You’re passing a block ID (from the page’s block children) into your notionData([],*id*) call, which expects a database_id. On pages, those first blocks are usually either:

• a linked database block (type: "link_to_database"). In this case the database_id lives at block.link_to_database.database_id (the block’s own id is not a database). Also note: the Notion API can’t query linked data sources directly—you must use the original database’s ID and the integration must be connected to that source. citeturn1search5
• an inline (child) database block (type: "child_database"). In practice you can use that block’s id as the database_id, but you should still verify by calling Retrieve database first. citeturn1search10turn0search0

Minimal fix: resolve a proper database_id from the block before querying

Drop this helper into your library and call it for scopeID/feesID instead of passing the raw block id.

/**
 * Given a block object from getNotionPageContent(...).results[i],
 * return a valid database_id or null + a reason string.
 */
function resolveDatabaseIdFromBlock_(block) {
  if (!block || typeof block !== 'object') return { id: null, reason: 'No block' };

  var t = block.type;
  if (t === 'link_to_database' && block.link_to_database && block.link_to_database.database_id) {
    return { id: block.link_to_database.database_id, reason: 'link_to_database.database_id' };
  }

  if (t === 'child_database') {
    // Often the child_database block id works as the database_id, but validate:
    return { id: block.id, reason: 'child_database block id (will validate)' };
  }

  return { id: null, reason: 'Block is type '+ t +' (not a database block)' };
}

/**
 * Optional: sanity check a database_id by calling your existing wrapper that retrieves a database.
 * Replace ULC_NotionAPI.retrieveDatabase with whatever you have (or use UrlFetchApp to /v1/databases/{id}).
 */
function validateDatabaseId_(databaseId) {
  try {
    var db = ULC_NotionAPI.retrieveDatabase(databaseId); // must return {object:"database", id:...} on success
    return db && db.object === 'database';
  } catch (e) {
    return false;
  }
}

Now patch your function so it resolves and validates the IDs before calling notionData:

function getNotionInvoiceTables(databaseid, index) {
  Logger.log(databaseid);

  var data = ULC_NotionAPI.notionData([], databaseid);           // top-level DB query
  var page = ULC_NotionAPI.getNotionPageContent(data[index].id); // blocks of that page

  // -- Resolve "Scope" database_id
  var scopeBlock = page.results[0];
  var scopeRes = resolveDatabaseIdFromBlock_(scopeBlock);
  if (!scopeRes.id) {
    throw new Error('Scope block is not a database: ' + scopeRes.reason);
  }
  if (!validateDatabaseId_(scopeRes.id)) {
    throw new Error('Scope database_id failed validation: ' + scopeRes.id +
                    '\nHint: If this is a linked database, you must use the ORIGINAL database_id and share it with the integration.');
  }

  // -- Resolve "Fees" database_id
  var feesBlock = page.results[1];
  var feesRes = resolveDatabaseIdFromBlock_(feesBlock);
  if (!feesRes.id) {
    throw new Error('Fees block is not a database: ' + feesRes.reason);
  }
  if (!validateDatabaseId_(feesRes.id)) {
    throw new Error('Fees database_id failed validation: ' + feesRes.id +
                    '\nHint: If this is a linked database, you must use the ORIGINAL database_id and share it with the integration.');
  }

  // Use the resolved database_ids instead of raw block ids:
  var scopeDB = ULC_NotionAPI.notionData([], scopeRes.id);
  var feesDB  = ULC_NotionAPI.notionData([], feesRes.id);

  var databases = [scopeDB, feesDB];
  var myvalues = [];
  var propertynames = [];
  var iterations = [];

  for (var d = 0; d < databases.length; d++) {
    var cur_DB = databases[d];
    var mycurvalues = [];
    var curpropertynames = Object.keys(cur_DB[0].properties);
    for (var i = 0; i < cur_DB.length; i++) {
      var properties = cur_DB[i].properties;
      for (var p in properties) {
        var prop = properties[p];
        mycurvalues.push(ULC_NotionAPI.getNotionProperties(prop, prop.type));
      }
    }
    propertynames.push(curpropertynames);
    myvalues.push(ULC_Sheets.splitToChunks(mycurvalues, cur_DB.length));
    iterations.push(cur_DB.length);
  }

  // ... return whatever you expect here
}

Why this fixes your exact 400

• scopeID was mypageid.results[0].id, i.e., a block id. Passing it to a database query endpoint triggers “Provided ID … is a block, not a database.”
• With the resolver:
  • If the block is linked, we pull the real database_id from block.link_to_database.database_id and use that (and remember: the API doesn’t operate on the linked view; it queries the original database you just referenced). citeturn1search5
  • If the block is child_database, we use its id but validate it via Retrieve database first to avoid blind 400s. citeturn1search0

If you want, paste a single page.results[i] sample (redact titles), and I’ll point to the exact field that carries the correct database_id.

You

Error: Scope block is not a database: Block is type paragraph (not a database block)

HOW THE FUCK IS THIS POSSIBLE?

Other db entries of the SAME db don’t throw this error

ChatGPT

[Unsupported Content]

ChatGPT

[Unsupported Content]

ChatGPT

Totally get the frustration 😅 — but the error actually makes perfect sense given how your code picks the blocks.

You’re assuming that:

var scopeID = mypageid.results[0].id;  // first block
var feesID  = mypageid.results[1].id;  // second block

…are database blocks. On some pages they are (so it works), but on others the first blocks are just paragraphs, callouts, toggles, synced blocks, etc. Notion pages aren’t guaranteed to start with your database blocks or keep them in a fixed position. Different entries in the same database can have different block structures (different templates, users added text above, etc).

The fix: find the database blocks instead of assuming positions

Scan the page’s blocks (and, if needed, their children) until you find blocks of type:

• child_database (inline database), or
• link_to_database (linked view → use its link_to_database.database_id)

Then use those IDs. If there aren’t two database blocks, throw a clear error listing what you actually found.

Here’s a drop-in, defensive implementation that:

• walks the page blocks recursively (because DB blocks might live under toggles, columns, callouts, synced blocks, etc.),
• resolves a proper database_id for each match,
• returns the first N database IDs (you need 2: Scope + Fees),
• logs what it sees so you can debug odd pages.

I’m assuming you have ULC_NotionAPI.getNotionPageContent(id) returning {results, next_cursor, has_more}, and ULC_NotionAPI.getNotionBlockChildren(blockId) that does the same for a block’s children. If you don’t have getNotionBlockChildren, you can implement it exactly like page content but call /blocks/{block_id}/children.

/**
 * Return up to `limit` database_ids discovered in a page by scanning blocks recursively.
 * Resolves both child_database and link_to_database. Very defensive + verbose.
 */
function resolveDatabaseIdsFromPage_(pageId, limit) {
  limit = limit || 2;
  var found = [];
  var seen = 0;

  // BFS over blocks (page content and any children)
  var queue = [{ id: pageId, kind: 'page' }];

  function summarizeBlock(b) {
    var t = b && b.type;
    var id = b && b.id;
    return (t || 'unknown') + ' ' + (id || '');
  }

  var seenSummaries = []; // for debug

  while (queue.length && found.length < limit) {
    var item = queue.shift();

    // Fetch children for page or block
    var resp;
    try {
      if (item.kind === 'page') {
        resp = ULC_NotionAPI.getNotionPageContent(item.id);
      } else {
        // needs an API that lists children of a block
        resp = ULC_NotionAPI.getNotionBlockChildren(item.id);
      }
    } catch (e) {
      throw new Error('Failed to fetch children for ' + item.kind + ' ' + item.id + ': ' + e);
    }

    if (!resp || !Array.isArray(resp.results)) {
      throw new Error('Invalid response listing children for ' + item.kind + ' ' + item.id + ': ' + JSON.stringify(resp));
    }

    for (var i = 0; i < resp.results.length && found.length < limit; i++) {
      var block = resp.results[i];
      seen++;
      seenSummaries.push(summarizeBlock(block));

      // --- database resolvers ---
      if (block.type === 'child_database') {
        // inline database: its block id doubles as the database_id, but validate later if you want
        found.push({ database_id: block.id, via: 'child_database', block: block });
        continue;
      }

      if (block.type === 'link_to_database' &&
          block.link_to_database &&
          block.link_to_database.database_id) {
        found.push({ database_id: block.link_to_database.database_id, via: 'link_to_database', block: block });
        continue;
      }

      // --- recurse if this block has children (DBs might be nested) ---
      if (block.has_children) {
        queue.push({ id: block.id, kind: 'block' });
      }
    }

    // handle pagination on the current node
    while (resp.has_more && found.length < limit) {
      try {
        resp = ULC_NotionAPI.getNotionBlockChildren(resp.next_cursor); // or your function variant that accepts cursor
      } catch (e2) {
        throw new Error('Pagination failed fetching children: ' + e2);
      }
      if (!resp || !Array.isArray(resp.results)) break;

      for (var j = 0; j < resp.results.length && found.length < limit; j++) {
        var block2 = resp.results[j];
        seen++;
        seenSummaries.push(summarizeBlock(block2));

        if (block2.type === 'child_database') {
          found.push({ database_id: block2.id, via: 'child_database', block: block2 });
          continue;
        }
        if (block2.type === 'link_to_database' &&
            block2.link_to_database &&
            block2.link_to_database.database_id) {
          found.push({ database_id: block2.link_to_database.database_id, via: 'link_to_database', block: block2 });
          continue;
        }
        if (block2.has_children) {
          queue.push({ id: block2.id, kind: 'block' });
        }
      }
    }
  }

  if (found.length === 0) {
    throw new Error(
      'No database blocks found in page ' + pageId + '.\n' +
      'First blocks encountered (' + Math.min(seenSummaries.length, 15) + ' shown):\n- ' +
      seenSummaries.slice(0, 15).join('\n- ') +
      '\nHint: On some entries the DBs may be nested under toggles/callouts/columns/synced blocks, ' +
      'or the page simply does not include those linked/inline databases.'
    );
  }

  return found.slice(0, limit);
}

Use it in your function

Replace the hardcoded [0] and [1] picks:

function getNotionInvoiceTables(databaseid, index) {
  Logger.log(databaseid);

  var data = ULC_NotionAPI.notionData([], databaseid);             // main DB query
  var pageId = data[index].id;

  // Find Scope + Fees DBs by scanning the page
  var resolved = resolveDatabaseIdsFromPage_(pageId, 2);

  if (resolved.length < 2) {
    throw new Error(
      'Found only ' + resolved.length + ' database block(s) on page ' + pageId + '. ' +
      'Need 2 (Scope & Fees). Found: ' + JSON.stringify(resolved)
    );
  }

  var scopeDBId = resolved[0].database_id;
  var feesDBId  = resolved[1].database_id;

  // (Optional) validate they’re real databases with your retrieveDatabase wrapper
  // if (!validateDatabaseId_(scopeDBId) || !validateDatabaseId_(feesDBId)) { ... }

  var scopeDB = ULC_NotionAPI.notionData([], scopeDBId);
  var feesDB  = ULC_NotionAPI.notionData([], feesDBId);

  var databases = [scopeDB, feesDB];
  var myvalues = [];
  var propertynames = [];
  var iterations = [];

  for (var d = 0; d < databases.length; d++) {
    var cur_DB = databases[d];
    if (!cur_DB || !cur_DB.length) {
      throw new Error('Query returned no rows for DB #' + d + ' (id: ' + (d === 0 ? scopeDBId : feesDBId) + ')');
    }
    var mycurvalues = [];
    var curpropertynames = Object.keys(cur_DB[0].properties || {});
    for (var i = 0; i < cur_DB.length; i++) {
      var properties = cur_DB[i].properties || {};
      for (var p in properties) {
        var prop = properties[p];
        mycurvalues.push(ULC_NotionAPI.getNotionProperties(prop, prop.type));
      }
    }
    propertynames.push(curpropertynames);
    myvalues.push(ULC_Sheets.splitToChunks(mycurvalues, cur_DB.length));
    iterations.push(cur_DB.length);
  }

  // return whatever you need; for example:
  return {
    scope: { id: scopeDBId, rows: databases[0] },
    fees:  { id: feesDBId,  rows: databases[1] },
    propertynames: propertynames,
    values: myvalues,
    iterations: iterations
  };
}

Why your pages behave differently

• Different entries used different templates (or users edited content), moving your database blocks away from positions 0 and 1.
• Sometimes the DBs are nested (toggle/callout/columns/synced block). If you don’t recurse, you’ll miss them.
• Some pages may not include the linked/inline DBs at all (e.g., the author deleted them on that page).

This approach removes the fragile “first two blocks” assumption and makes your function robust to real-world Notion page layouts. If you paste one page’s first ~10 blocks (just block type and has_children), I can show exactly where your DBs are hiding on that one.

You

Ok. it works!!

Let’s clean up the entire library. After cleaning up the code, let me know which parts of it should better exist as reusable components in a library, and which ones are fine as functions specific to this doc

//Notion database id for Income[MAIN] //databaseid = ‘055ac6e934044befa59a8db1c34a4e86’; function gdoc_addTableData(doc,rowdata,currency_symbol){ // arguments —> Google Document body, row data list, currency symbol

/* //for debugging only const drivefile = DriveApp.getFileById(‘1JUaaz4ij9J7yfW6y3uwflS6zTHVBpN5Fk4skOFsH7Cs’); const doc = DocumentApp.openById(drivefile.getId()); // open document var rowdata = [[“one”,“two”,“three”,“four”],[“five”,“six”,“seven”,“eight”]] */

var body = doc.getBody(); //get body var _templatetables = body.getTables(); var _templatetable = _templatetables[0]; var _tablerows = rowdata.length;

///* for (var r = 0; r < _tablerows; r++) { //loop through entries from Notion

var newrow = _templatetable.insertTableRow(1+r)

//add cells and values - this part is hardcoded because the properties list from Notion is not in the same order as we want it - sorting it could be a bit tricky
var _description = newrow.appendTableCell(rowdata[r][3]) //Description
var _quantity = newrow.appendTableCell(rowdata[r][0].toString()) //Quantity
var _unitprice = newrow.appendTableCell(currency_symbol + rowdata[r][2].toString()) //Unit Price
var _amount = newrow.appendTableCell(currency_symbol + rowdata[r][1].toString()) //Amount

//center cell values
var cellStyle = {};
cellStyle[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.CENTER;

_description.getChild(0).asParagraph().setAttributes(cellStyle)
_quantity.getChild(0).asParagraph().setAttributes(cellStyle)
_unitprice.getChild(0).asParagraph().setAttributes(cellStyle)
_amount.getChild(0).asParagraph().setAttributes(cellStyle)

}

///*//this code changes cell border style for the new rows - only between the 2row and n-1row doc.saveAndClose(); var docId = doc.getId(); var i = body.getChildIndex(_templatetable); var index = Docs.Documents.get(docId).body.content[i + 1].startIndex; var requests = [{ updateTableCellStyle: { tableCellStyle: { borderBottom: { dashStyle: “SOLID”, width: { magnitude: 1, unit: “PT” }, color: { color: { rgbColor: { red: 0 } } } },borderRight: { dashStyle: “SOLID”, width: { magnitude: 0, unit: “PT” }, color: { color: {} } }, borderLeft: { dashStyle: “SOLID”, width: { magnitude: 0, unit: “PT” }, color: { color: {} } } }, tableRange: { tableCellLocation: { tableStartLocation: { index }, rowIndex: 1 }, rowSpan: _tablerows, columnSpan: rowdata[0].length }, fields: “borderBottom,borderRight,borderLeft” } }];

Docs.Documents.batchUpdate({ requests }, doc.getId()); //*/

//*/ //Logger.log(rowdata) //Logger.log(getNotionInvoiceTables(‘055ac6e934044befa59a8db1c34a4e86’,0)) }

function gdoc_addBulletPointsBelow(doc,parent,mybulletlist){ // arguments —> Google Document body, parent text (string), bullet point list

/*//for debugging only var mybulletlist = [“This”,“That”,“The other one”]; var parent = “Scope of Work” ; const docfile = DriveApp.getFileById(‘1JUaaz4ij9J7yfW6y3uwflS6zTHVBpN5Fk4skOFsH7Cs’); const body = DocumentApp.openById(docfile.getId()).getBody(); // open document */ var body = doc.getBody(); var paragraphs = body.getParagraphs(); for (var i = 0; i < paragraphs.length; i++) { var text = paragraphs[i].getText();

if (text === parent) {
  //add bullet points for every Scope entry from Notion in a for loop
  for (var b = 0; b < mybulletlist.length; b++) {
    var style = {};
    style[DocumentApp.Attribute.FONT_SIZE] = 12;
    style[DocumentApp.Attribute.FOREGROUND_COLOR] = '#000000';
    var listItem = body.insertListItem(i + 1 + b, mybulletlist[b]);
    listItem.setGlyphType(DocumentApp.GlyphType.BULLET);
    listItem.setAttributes(style)
  }
}

} }

/**

• Given a block object from getNotionPageContent(…).results[i],
• return a valid database_id or null + a reason string. */ function resolveDatabaseIdFromBlock_(block) { if (!block || typeof block !== ‘object’) return { id: null, reason: ‘No block’ };

var t = block.type; if (t === ‘link_to_database’ && block.link_to_database && block.link_to_database.database_id) { return { id: block.link_to_database.database_id, reason: ‘link_to_database.database_id’ }; }

if (t === ‘child_database’) { // Often the child_database block id works as the database_id, but validate: return { id: block.id, reason: ‘child_database block id (will validate)’ }; }

return { id: null, reason: ‘Block is type ’+ t +’ (not a database block)’ }; }

/**

• Optional: sanity check a database_id by calling your existing wrapper that retrieves a database.
• Replace ULC_NotionAPI.retrieveDatabase with whatever you have (or use UrlFetchApp to /v1/databases/{id}). */ function validateDatabaseId_(databaseId) { try { var db = ULC_NotionAPI.retrieveDatabase(databaseId); // must return {object:“database”, id:…} on success return db && db.object === ‘database’; } catch (e) { return false; } }

/**

• Return up to limit database_ids discovered in a page by scanning blocks recursively.
• Resolves both child_database and link_to_database. Very defensive + verbose. */ function resolveDatabaseIdsFromPage_(pageId, limit) { limit = limit || 2; var found = []; var seen = 0;

// BFS over blocks (page content and any children) var queue = [{ id: pageId, kind: ‘page’ }];

function summarizeBlock(b) { var t = b && b.type; var id = b && b.id; return (t || ‘unknown’) + ’ ’ + (id || ”); }

var seenSummaries = []; // for debug

while (queue.length && found.length < limit) { var item = queue.shift();

// Fetch children for page or block
var resp;
try {
  if (item.kind === 'page') {
    resp = ULC_NotionAPI.getNotionPageContent(item.id);
  } else {
    // needs an API that lists children of a block
    resp = ULC_NotionAPI.getNotionBlockChildren(item.id);
  }
} catch (e) {
  throw new Error('Failed to fetch children for ' + item.kind + ' ' + item.id + ': ' + e);
}

if (!resp || !Array.isArray(resp.results)) {
  throw new Error('Invalid response listing children for ' + item.kind + ' ' + item.id + ': ' + JSON.stringify(resp));
}

for (var i = 0; i < resp.results.length && found.length < limit; i++) {
  var block = resp.results[i];
  seen++;
  seenSummaries.push(summarizeBlock(block));

  // --- database resolvers ---
  if (block.type === 'child_database') {
    // inline database: its block id doubles as the database_id, but validate later if you want
    found.push({ database_id: block.id, via: 'child_database', block: block });
    continue;
  }

  if (block.type === 'link_to_database' &&
      block.link_to_database &&
      block.link_to_database.database_id) {
    found.push({ database_id: block.link_to_database.database_id, via: 'link_to_database', block: block });
    continue;
  }

  // --- recurse if this block has children (DBs might be nested) ---
  if (block.has_children) {
    queue.push({ id: block.id, kind: 'block' });
  }
}

// handle pagination on the current node
while (resp.has_more && found.length < limit) {
  try {
    resp = ULC_NotionAPI.getNotionBlockChildren(resp.next_cursor); // or your function variant that accepts cursor
  } catch (e2) {
    throw new Error('Pagination failed fetching children: ' + e2);
  }
  if (!resp || !Array.isArray(resp.results)) break;

  for (var j = 0; j < resp.results.length && found.length < limit; j++) {
    var block2 = resp.results[j];
    seen++;
    seenSummaries.push(summarizeBlock(block2));

    if (block2.type === 'child_database') {
      found.push({ database_id: block2.id, via: 'child_database', block: block2 });
      continue;
    }
    if (block2.type === 'link_to_database' &&
        block2.link_to_database &&
        block2.link_to_database.database_id) {
      found.push({ database_id: block2.link_to_database.database_id, via: 'link_to_database', block: block2 });
      continue;
    }
    if (block2.has_children) {
      queue.push({ id: block2.id, kind: 'block' });
    }
  }
}

}

if (found.length === 0) { throw new Error( ‘No database blocks found in page ’ + pageId + ’.\n’ + ‘First blocks encountered (’ + Math.min(seenSummaries.length, 15) + ’ shown):\n- ’ + seenSummaries.slice(0, 15).join(‘\n- ’) + ‘\nHint: On some entries the DBs may be nested under toggles/callouts/columns/synced blocks, ’ + ‘or the page simply does not include those linked/inline databases.’ ); }

return found.slice(0, limit); }

function getNotionInvoiceTables(databaseid,index){ //pass a database id and an index - the index is needed to iterate this whole function for every database item inside the createInvoiceDocument function Logger.log(databaseid);

var data = ULC_NotionAPI.notionData([], databaseid); // main DB query var pageId = data[index].id;

// Find Scope + Fees DBs by scanning the page var resolved = resolveDatabaseIdsFromPage_(pageId, 2);

if (resolved.length < 2) { throw new Error( ‘Found only ’ + resolved.length + ’ database block(s) on page ’ + pageId + ’. ’ + ‘Need 2 (Scope & Fees). Found: ’ + JSON.stringify(resolved) ); }

var scopeDBId = resolved[0].database_id; var feesDBId = resolved[1].database_id;

var scopeDB = ULC_NotionAPI.notionData([], scopeDBId); var feesDB = ULC_NotionAPI.notionData([], feesDBId);

var databases = [scopeDB,feesDB]; var myvalues = []; var propertynames = []; var iterations = [];

for (var d=0; d<databases.length; d++) {

var cur_DB = databases[d];
var mycurvalues = [];

var curpropertynames = Object.keys(cur_DB[0]["properties"]);

for (var i = 0; i < cur_DB.length; i++) {
  var properties = cur_DB[i]["properties"];

  for (p in properties) {
    var myproperty = properties[p]
    var type = myproperty["type"];
    mycurvalues.push(ULC_NotionAPI.getNotionProperties(myproperty, type));
  }
}


propertynames.push(curpropertynames)
myvalues.push(ULC_Sheets.splitToChunks(mycurvalues,cur_DB.length))
iterations.push(cur_DB.length)

}

//Logger.log(propertynames) //Logger.log(myvalues) //Logger.log(iterations) return [propertynames,myvalues,iterations]; }

function getDriveFolderByName(rootfolder, foldername) { var myfolder; var folders = rootfolder.getFoldersByName(foldername) while (folders.hasNext()) { myfolder = folders.next(); } return myfolder; }

function getDriveFileByName(rootfolder, filename) { var myfile; var files = rootfolder.getFilesByName(filename) while (files.hasNext()) { myfile = files.next(); } return myfile; }

function moveFileId(fileId, toFolderId) { var file = DriveApp.getFileById(fileId); var source_folder = DriveApp.getFileById(fileId).getParents().next(); var destinationfolder = DriveApp.getFolderById(toFolderId) destinationfolder.addFile(file); source_folder.removeFile(file); }

//This function is to auto-populate our Invoice document template for any new payments that do not have one yet //Google Trigger every minute function createInvoiceDocument(){

//Income[MAIN] Notion Database id const databaseid = ‘055ac6e934044befa59a8db1c34a4e86’;

//Get Notion database - pick properties var data = ULC_NotionAPI.notionData([“Name”, “Amount”, “From”, “Invoice Reference”, “Date Invoiced”, “Invoice URL”, “Client Code”, “Project Code”,“Create Invoice Document”, “Days Notice”, “Address”, “Tax_ID”, “Id”, “Type Code”,“Currency”],databaseid)

//Logger.log(data[data.length-1]) //Logger.log(data[0][“id”]) //used for debugging only

// const googleDocTemplate = DriveApp.getFileById(‘1cyK6pLkBQD1wAkJph39xhMZnzQrxKe3dlm6eOUM2lkw’);// this is for the Debugging Template

const googleDocTemplate = DriveApp.getFileById(‘1crhh7fZNUvQGQTgm_4NTcVJhEfm0sD58C6PtqYiH4eU’); // get Google Document Invoice Template for GBP const googleDocTemplate_EURO = DriveApp.getFileById(‘1Qintc9Z0PQGumQjouhN1Gb1GQvBNm9WrMBX6rxHP4FQ’); // get Google Document Invoice Template for EURO const googleDocTemplate_USD = DriveApp.getFileById(‘1vKOJAWj-zP4EcEI1bJYEx4o8GdR3g85V3n4R1bw3ytY’); // get Google Document Invoice Template for USD const googleDocTemplate_CAD = DriveApp.getFileById(‘1hBuPfsp99gWw9Kgj33iQKc_EhClHHif5iX6K-baz0d0’); // get Google Document Invoice Template for Canadian Dollars

var _invoicefolder = DriveApp.getFolderById(“1-mkKKkUK35VMUoHE1gNDbytPWbbOkvvO”); //get Google Drive folder we keep all our Invoices var _invoicefolders = _invoicefolder.getFolders(); // get subfolders var invoiceClientsFolder = []; var invoiceProjectsFolder = [];

//iterate thorough all Client Invoice Folders and their Project Invoice subfolders and store their folder names in two arrays while (_invoicefolders.hasNext()) { var folder = _invoicefolders.next(); var _projectfolders = folder.getFolders(); while (_projectfolders.hasNext()) { var projfolder = _projectfolders.next(); invoiceProjectsFolder.push(projfolder.getName()) } invoiceClientsFolder.push(folder.getName())

//Logger.log(folder.getName());

}

Logger.log(“Invoice Client Folders : ” + invoiceClientsFolder) Logger.log(“Invoice Project Folders : ” + invoiceProjectsFolder)

//loop through database entries for (var i = 0; i < data.length; i++) {

var _invoiceUrl = data[i].properties["Invoice URL"]['url'];
var _createDoc = data[i].properties["Create Invoice Document"]["checkbox"];

// Logger.log(data[i].properties["Invoice Reference"]["formula"]["string"])

//if property "Invoice URL" is empty and Admin approved Document Creation then continue (This is to avoid getting a document created midway of filling out all the details)
if (_invoiceUrl == null && _createDoc == 1) {

  //get all neccessary property values
  var _client = data[i].properties["From"]["rollup"]["array"][0]["select"]["name"];
  var _clientcode = data[i].properties["Client Code"]["rollup"]["array"][0]["select"]["name"];
  var _projectcode = data[i].properties["Project Code"]["rollup"]["array"][0]["select"]["name"];
  var _invoicename = data[i].properties["Name"]["title"][0]["plain_text"];
  var _dateinvoiced = data[i].properties["Date Invoiced"]["date"]["start"];
  var _invoiceRef = data[i].properties["Invoice Reference"]["formula"]["string"];
  var _amount = data[i].properties["Amount"]["number"];
  var _notice = data[i].properties["Days Notice"]["number"];
  var _address = data[i].properties["Address"]["rollup"]["array"][0]["rich_text"][0]

  // Below line Added by George Adamopoulos
  var _tax_id = data[i].properties["Tax_ID"]["rollup"]["array"][0]["rich_text"][0]

  var _id = data[i].properties["Id"]["rich_text"][0];
  var _type_code = data[i].properties["Type Code"]["rich_text"][0];
  if (_type_code != null) { _type_code = _type_code["plain_text"] }
  if (_id != null) { _id = _id["plain_text"] }

  if (_address != null) {
    _address = _address["plain_text"]
  }else {_address = ""}

  // Below line Added by George Adamopoulos
  if (_tax_id != null) {
    _tax_id = _tax_id["plain_text"]
  }else {_tax_id = ""}

  var _currency = data[i].properties["Currency"]["select"]["name"]
  Logger.log(_currency)

  //INVOICE REF FIX - this is a temporary fix until Notion fixes the bug preventing us from reading our formula that contained the invoice ref num.
  //so we make it from scratch in here
  // _invoiceRef = invoiceRef_fix(_clientcode,_projectcode,_type_code,_id)

  //check data///////////////////////////
  //Logger.log(data[i].properties["Invoice Reference"]);
  // Logger.log(_invoiceRef);
  ////////////////////////////////////////

  Logger.log("Preparing an invoice document: " + _invoicename + " ,for client: " + _client + " ,project : " + _projectcode)

  var _pageid = data[i]["id"]; //get the page id in order to change the "Invoice Url" property value later

  //get values for tables [[propertynames], [values], [entries per database]]
  var _mytables = getNotionInvoiceTables(databaseid,i);



  Logger.log("Bullet Points : " + _mytables[1][0]);

  var myvalues = [_client, _clientcode, _projectcode, _invoicename, _dateinvoiced, _invoiceRef, _amount, _notice]; //store all the above values in an array to check if any are missing

  //proceed only if we have all necessary values
  if (myvalues.every(x => x !== null)) {

    Logger.log("There is an Invoice URL missing for client : " + _client + " ,project : " + _projectcode)
    Logger.log("Item with no Invoice Document : " + _invoicename + " invoiced on " + _dateinvoiced)

    var cur_clientfolder;
    var cur_projectfolder;

    //check invoice folders, if client folder does not exist, create it using _client
    if (invoiceClientsFolder.includes(_client)) {

      Logger.log("Invoice client folder : " + _client + " exists")
      cur_clientfolder = getDriveFolderByName(_invoicefolder, _client); //get that folder

      if (invoiceProjectsFolder.includes(_projectcode)) {

        Logger.log("Invoice project folder : " + _projectcode + " exists")

        cur_projectfolder = getDriveFolderByName(cur_clientfolder, _projectcode); //get that folder

      } else {
        Logger.log("Invoice Project folder : " + _projectcode + " does not exist....creating it now!")
        //create new Google Drive Folder
        cur_projectfolder = cur_clientfolder.createFolder(_projectcode);
      }
    } else {
      Logger.log("Invoice Client folder : " + _client + " does not exist....creating it now!")
      Logger.log("Invoice Project folder : " + _projectcode + " does not exist....creating it now!")
      //create new Google Drive folder
      cur_clientfolder = _invoicefolder.createFolder(_client);
      //create sub folder for project
      cur_projectfolder = cur_clientfolder.createFolder(_projectcode);
    }

    const docname = _dateinvoiced.replace(/-/g, "").slice(2) + "_" + _clientcode + "_" + _invoicename; //create filename
    var copy;

    //check if there is a document with the same filename already there
    //Here we need to know what to do in case we already have a document we want to overwrite - for now we are playing it safe contacting Admin
    if (getDriveFileByName(cur_projectfolder, docname)) {
      //if the file already exists notify Admins
      Logger.log("There is another file with the same name already in that folder ,notifying admin via email")
      MailApp.sendEmail("studio@uncharted-limbo.com", "Invoice for : " + _invoicename, "An Invoice document was found in this folder : " + cur_projectfolder.getUrl() + " ,but it's not connected to Notion Income Database. Invoice is for client : " + _client + " , Document name : "  + docname)
    } else {
      //if the file doesnt exist continue
      Logger.log("Copied Invoice Template to Client Invoice Folder " + _client + "/" + _projectcode)

      if (_currency == "EUR")
      {
        copy = googleDocTemplate_EURO.makeCopy(docname, cur_projectfolder);
      }
      else if (_currency == "USD")
      {
        copy = googleDocTemplate_USD.makeCopy(docname, cur_projectfolder);
      }
      else if (_currency == "CAD")
      {
        copy = googleDocTemplate_CAD.makeCopy(docname, cur_projectfolder);
      }
      else
      {
        //copy Google Document template to the destination project folder cur_projectfolder
        copy = googleDocTemplate.makeCopy(docname, cur_projectfolder);
      }

      //get body contents of Document
      const doc = DocumentApp.openById(copy.getId()); // open document
      const body = doc.getBody(); // get body of document

      //replace body contents with notion data
      const currency_symbol = getcurrency_symbol(_currency) //get currency symbol from acronym
      body.replaceText('{{Invoice Reference}}', _invoiceRef);
      body.replaceText('{{Date Invoiced}}', _dateinvoiced);
      body.replaceText('{{Client}}', _client);
      body.replaceText('{{Amount}}', currency_symbol + _amount);

      const _vat = 0.2;
      const _vatamount = _amount * _vat;
      const _totalamount = _amount + _vatamount;
      body.replaceText('{{VAT}}', currency_symbol + _vatamount);
      body.replaceText('{{TotalAmount}}', currency_symbol + _totalamount);

      body.replaceText('{{Notice}}', _notice);
      body.replaceText('{{Currency}}', _currency);
      body.replaceText('{{Address}}', _address);

      // Below line Added by George Adamopoulos
      body.replaceText('{{Tax_ID}}', _tax_id);

      //Scope of Work bullet points
      var bulletpoints = ULC_Sheets.flatten(_mytables[1][0]);

      /*
      //check if bullet points are not the defaults
      const subArr = bulletpoints.filter(str => str.includes('__[TMP]__'));
      if(len(subArr) > 0){
        //send email stop doc creation
      }else{
        //add bullets continue with doc creation
      }
      */

      gdoc_addBulletPointsBelow(doc,"Scope of Work",bulletpoints);

      //Agreed Fee Structure table
      var tabledata = _mytables[1][1];
      Logger.log(doc);

      gdoc_addTableData(doc,tabledata, currency_symbol)

      Logger.log("Replaced placeholders with Invoice entry values from Notion")

      //save and close document
      //doc.saveAndClose();
      Logger.log("Document is saved and closed")

      //save as PDF
      Logger.log("Converting to PDF")

      var pdfdoc = doc.getAs('application/pdf');
      /* Add the PDF extension */
      pdfdoc.setName(doc.getName() + ".pdf");
      var pdffile = DriveApp.createFile(pdfdoc);

      Logger.log("Created new PDF file")

      var fileId = pdffile.getId();
      var folderid = DriveApp.getFileById(doc.getId()).getParents().next().getId();
      moveFileId(fileId, folderid);

      //Store the url of our new document in a variable
      const pdf_url = pdffile.getUrl().toString();

      //Write this value back to Notion property "Invoice URL"
      let props = {
        "properties": {
          "Invoice URL": {
            "url": pdf_url
          }
        }
      };

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// ///NEED TO ACCESS API DAILY AND GRAB LATEST RATES --- FOR NOW SIMPLE WORKAROUND IS TO GO BACK TO NOTION AFTER THE INVOICE DOC IS CREATED AND PASTE THE GBP CONVERTED VALUE TO THE AMOUNT

      if(_currency != "GBP"){
        //CONVERT the amount to GBP and set the Notion property value
      }

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

      ULC_NotionAPI.setNotionPropertyValue(_pageid,props)
      Logger.log("Invoice Document URL was imported back to Notion property -Invoice URL- : " + pdf_url)

      //send email to ULC notifying that the new Invoice Document was created - attach link to doc
      MailApp.sendEmail("studio@uncharted-limbo.com","Invoice for : " + _invoicename,"A new Invoice Document was created for client : " + _client + " and project : " + _projectcode + " Open link : " + pdf_url );
      Logger.log("Email was sent to studio@uncharted-limbo.com for Invoice : " + _invoicename)
    }
  } else {

    Logger.log("There are values missing from Notion")

  var myvalues = [_client, _clientcode, _projectcode, _invoicename, _dateinvoiced, _invoiceRef, _amount, _notice]; //store all the above values in an array to check if any are missing

    var badvalues = ""
    if (myvalues[0] === null)
    {
      badvalues += " * Client\n"
    }
    if (myvalues[1] === null)
    {
      badvalues += " * Client Code\n"
    }
    if (myvalues[2] === null)
    {
      badvalues += " * Project Code\n"
    }
    if (myvalues[3] === null)
    {
      badvalues += " * Invoice Name\n"
    }
    if (myvalues[4] === null)
    {
      badvalues += " * Date Invoiced\n"
    }
    if (myvalues[5] === null)
    {
      badvalues += " * Invoice Ref\n"
    }
    if (myvalues[6] === null)
    {
      badvalues += " * Amount\n"
    }
    if (myvalues[7] === null)
    {
      badvalues += " * Notice\n"
    }
    MailApp.sendEmail("studio@uncharted-limbo.com", "AppScript error - Invoice for : " + _invoicename, "Invoice Document for client : " + _client + " and project : " + _projectcode + " could not be created because some Notion properties were empty:\n" + badvalues)
  }
}

} }

ChatGPT

[Unsupported Content]

ChatGPT

[Unsupported Content]

ChatGPT

Awesome—glad it’s working! Here’s a cleaned, defensive, and modular version of your code, plus guidance on what belongs in a reusable library vs. what can stay project-specific.

---

Cleaned code (drop-in)

/** @OnlyCurrentDoc */
'use strict';

/* ===============================
 * CONFIG
 * =============================== */
const CONFIG = {
  NOTION: {
    INCOME_DB_ID: '055ac6e934044befa59a8db1c34a4e86',
    INCOME_FIELDS: [
      'Name', 'Amount', 'From', 'Invoice Reference', 'Date Invoiced', 'Invoice URL',
      'Client Code', 'Project Code', 'Create Invoice Document', 'Days Notice', 'Address',
      'Tax_ID', 'Id', 'Type Code', 'Currency'
    ]
  },
  DOC_TEMPLATES: {
    GBP: '1crhh7fZNUvQGQTgm_4NTcVJhEfm0sD58C6PtqYiH4eU',
    EUR: '1Qintc9Z0PQGumQjouhN1Gb1GQvBNm9WrMBX6rxHP4FQ',
    USD: '1vKOJAWj-zP4EcEI1bJYEx4o8GdR3g85V3n4R1bw3ytY',
    CAD: '1hBuPfsp99gWw9Kgj33iQKc_EhClHHif5iX6K-baz0d0'
  },
  DRIVE: {
    INVOICES_ROOT: '1-mkKKkUK35VMUoHE1gNDbytPWbbOkvvO'
  },
  EMAIL: {
    ADMIN: 'studio@uncharted-limbo.com'
  },
  INVOICE: {
    VAT_RATE: 0.20
  }
};

/* ===============================
 * SMALL UTILITIES (safe, pure)
 * =============================== */

function currencySymbol_(code) {
  switch (code) {
    case 'EUR': return '€';
    case 'USD': return '$';
    case 'CAD': return '$';
    case 'GBP':
    default: return '£';
  }
}

function safeGet(obj, path, fallback) {
  try {
    return path.reduce((o, k) => (o && o[k] !== undefined ? o[k] : undefined), obj) ?? fallback;
  } catch (e) {
    return fallback;
  }
}

function nonEmptyArray_(arr) {
  return Array.isArray(arr) && arr.length > 0;
}

/* ===============================
 * DRIVE HELPERS
 * =============================== */

function getDriveFolderByName(rootFolder, folderName) {
  const it = rootFolder.getFoldersByName(folderName);
  return it.hasNext() ? it.next() : null;
}

function getDriveFileByName(rootFolder, fileName) {
  const it = rootFolder.getFilesByName(fileName);
  return it.hasNext() ? it.next() : null;
}

function moveFileId(fileId, toFolderId) {
  const file = DriveApp.getFileById(fileId);
  const sourceFolder = file.getParents().hasNext() ? file.getParents().next() : null;
  const destinationFolder = DriveApp.getFolderById(toFolderId);
  destinationFolder.addFile(file);
  if (sourceFolder) sourceFolder.removeFile(file);
}

/* ===============================
 * DOCS HELPERS
 * =============================== */

/**
 * Insert rows to the first table in a Doc with [qty, amount, unitprice, description] mapping.
 * rowData: [ [qty, amount, unitPrice, description], ... ]
 */
function gdoc_addTableData(doc, rowData, currencySymbol) {
  const body = doc.getBody();
  const tables = body.getTables();
  if (!nonEmptyArray_(tables)) return;

  const table = tables[0];
  const rows = rowData.length;

  for (let r = 0; r < rows; r++) {
    const row = table.insertTableRow(1 + r);
    const description = row.appendTableCell(String(rowData[r][3] ?? ''));
    const quantity    = row.appendTableCell(String(rowData[r][0] ?? ''));
    const unitPrice   = row.appendTableCell(currencySymbol + String(rowData[r][2] ?? ''));
    const amount      = row.appendTableCell(currencySymbol + String(rowData[r][1] ?? ''));

    const center = {};
    center[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.CENTER;
    [description, quantity, unitPrice, amount].forEach(c => {
      if (c.getNumChildren() > 0) c.getChild(0).asParagraph().setAttributes(center);
    });
  }

  // Update borders for the inserted range via Docs API (needs Advanced Google Services: Docs)
  doc.saveAndClose();
  const docId = doc.getId();
  const tableBodyIndex = body.getChildIndex(table);
  const startIndex = Docs.Documents.get(docId).body.content[tableBodyIndex + 1].startIndex;

  const requests = [{
    updateTableCellStyle: {
      tableCellStyle: {
        borderBottom: { dashStyle: 'SOLID', width: { magnitude: 1, unit: 'PT' }, color: { color: { rgbColor: { red: 0 } } } },
        borderRight:  { dashStyle: 'SOLID', width: { magnitude: 0, unit: 'PT' }, color: { color: {} } },
        borderLeft:   { dashStyle: 'SOLID', width: { magnitude: 0, unit: 'PT' }, color: { color: {} } }
      },
      tableRange: {
        tableCellLocation: { tableStartLocation: { index: startIndex }, rowIndex: 1 },
        rowSpan: rows,
        columnSpan: rowData[0]?.length || 4
      },
      fields: 'borderBottom,borderRight,borderLeft'
    }
  }];

  Docs.Documents.batchUpdate({ requests }, docId);
}

function gdoc_addBulletPointsBelow(doc, parentText, bullets) {
  const body = doc.getBody();
  const paragraphs = body.getParagraphs();

  for (let i = 0; i < paragraphs.length; i++) {
    if (paragraphs[i].getText() === parentText) {
      for (let b = 0; b < bullets.length; b++) {
        const style = {};
        style[DocumentApp.Attribute.FONT_SIZE] = 12;
        style[DocumentApp.Attribute.FOREGROUND_COLOR] = '#000000';
        const li = body.insertListItem(i + 1 + b, String(bullets[b] ?? ''));
        li.setGlyphType(DocumentApp.GlyphType.BULLET);
        li.setAttributes(style);
      }
      break;
    }
  }
}

/* ===============================
 * NOTION HELPERS
 * (reuse across projects)
 * =============================== */

/** Resolve a database_id from a block object. */
function resolveDatabaseIdFromBlock_(block) {
  if (!block || typeof block !== 'object') return { id: null, reason: 'No block' };
  const t = block.type;

  if (t === 'link_to_database' && safeGet(block, ['link_to_database', 'database_id'], null)) {
    return { id: block.link_to_database.database_id, reason: 'link_to_database.database_id' };
  }
  if (t === 'child_database') {
    return { id: block.id, reason: 'child_database block id (validate recommended)' };
  }
  return { id: null, reason: 'Block is type ' + t + ' (not a database block)' };
}

/** Optional: use your wrapper to confirm this is a database. */
function validateDatabaseId_(databaseId) {
  try {
    const db = ULC_NotionAPI.retrieveDatabase(databaseId);
    return db && db.object === 'database';
  } catch (e) {
    return false;
  }
}

/**
 * BFS scan page content (and nested children) to find up to `limit` database_ids.
 * Requires: ULC_NotionAPI.getNotionPageContent, ULC_NotionAPI.getNotionBlockChildren.
 */
function resolveDatabaseIdsFromPage_(pageId, limit) {
  limit = limit || 2;
  const found = [];
  const queue = [{ id: pageId, kind: 'page' }];

  while (queue.length && found.length < limit) {
    const item = queue.shift();
    let resp;

    try {
      resp = (item.kind === 'page')
        ? ULC_NotionAPI.getNotionPageContent(item.id)
        : ULC_NotionAPI.getNotionBlockChildren(item.id);
    } catch (e) {
      throw new Error('Failed to fetch children for ' + item.kind + ' ' + item.id + ': ' + e);
    }

    const results = resp && resp.results;
    if (!Array.isArray(results)) {
      throw new Error('Invalid response listing children for ' + item.kind + ' ' + item.id + ': ' + JSON.stringify(resp));
    }

    for (let i = 0; i < results.length && found.length < limit; i++) {
      const block = results[i];

      if (block.type === 'child_database') {
        found.push({ database_id: block.id, via: 'child_database', block });
        continue;
      }
      if (block.type === 'link_to_database' && safeGet(block, ['link_to_database', 'database_id'], null)) {
        found.push({ database_id: block.link_to_database.database_id, via: 'link_to_database', block });
        continue;
      }
      if (block.has_children) queue.push({ id: block.id, kind: 'block' });
    }

    // paginate children if necessary (assuming your wrapper can accept a cursor)
    while (resp.has_more && found.length < limit) {
      try {
        resp = ULC_NotionAPI.getNotionBlockChildren(resp.next_cursor);
      } catch (e2) {
        throw new Error('Pagination failed fetching children: ' + e2);
      }
      const res2 = resp && resp.results;
      if (!Array.isArray(res2)) break;

      for (let j = 0; j < res2.length && found.length < limit; j++) {
        const b2 = res2[j];
        if (b2.type === 'child_database') {
          found.push({ database_id: b2.id, via: 'child_database', block: b2 });
          continue;
        }
        if (b2.type === 'link_to_database' && safeGet(b2, ['link_to_database', 'database_id'], null)) {
          found.push({ database_id: b2.link_to_database.database_id, via: 'link_to_database', block: b2 });
          continue;
        }
        if (b2.has_children) queue.push({ id: b2.id, kind: 'block' });
      }
    }
  }

  if (found.length === 0) {
    throw new Error('No database blocks found in page ' + pageId + '.');
  }
  return found.slice(0, limit);
}

/**
 * Get [propertyNames, values, rowCounts] for "Scope of Work" and "Agreed Fee Structure".
 * Returns: [ [propNamesScope, propNamesFees], [valuesScope, valuesFees], [rowsScope, rowsFees] ]
 */
function getNotionInvoiceTables(databaseId, index) {
  const data = ULC_NotionAPI.notionData([], databaseId);
  if (!nonEmptyArray_(data)) throw new Error('Income DB returned no rows.');
  const pageId = data[index]?.id;
  if (!pageId) throw new Error('Invalid page index ' + index);

  const resolved = resolveDatabaseIdsFromPage_(pageId, 2);
  if (resolved.length < 2) throw new Error('Could not find 2 database blocks on page ' + pageId);

  const scopeDBId = resolved[0].database_id;
  const feesDBId  = resolved[1].database_id;

  if (!validateDatabaseId_(scopeDBId) || !validateDatabaseId_(feesDBId)) {
    throw new Error('Resolved database IDs failed validation: ' + [scopeDBId, feesDBId].join(', '));
  }

  const scopeDB = ULC_NotionAPI.notionData([], scopeDBId);
  const feesDB  = ULC_NotionAPI.notionData([], feesDBId);

  const databases = [scopeDB, feesDB];
  const propertyNames = [];
  const values = [];
  const rowCounts = [];

  for (let d = 0; d < databases.length; d++) {
    const cur = databases[d];
    if (!nonEmptyArray_(cur)) {
      propertyNames.push([]);
      values.push([]);
      rowCounts.push(0);
      continue;
    }

    const propsFirst = cur[0].properties || {};
    const curPropNames = Object.keys(propsFirst);
    const curValuesFlat = [];

    for (let i = 0; i < cur.length; i++) {
      const props = cur[i].properties || {};
      for (const p in props) {
        const prop = props[p];
        curValuesFlat.push(ULC_NotionAPI.getNotionProperties(prop, prop.type));
      }
    }

    propertyNames.push(curPropNames);
    // If you prefer your ULC_Sheets helper, keep it. Otherwise chunk deterministically:
    values.push(ULC_Sheets.splitToChunks(curValuesFlat, cur.length));
    rowCounts.push(cur.length);
  }

  return [propertyNames, values, rowCounts];
}

/* ===============================
 * MAIN ORCHESTRATOR
 * =============================== */

function createInvoiceDocument() {
  const dbId = CONFIG.NOTION.INCOME_DB_ID;
  const data = ULC_NotionAPI.notionData(CONFIG.NOTION.INCOME_FIELDS, dbId);
  if (!nonEmptyArray_(data)) return;

  // Build quick lookup caches of existing client/project folders
  const invoicesRoot = DriveApp.getFolderById(CONFIG.DRIVE.INVOICES_ROOT);
  const clientFolders = [];
  const projectFolders = [];
  const clientIter = invoicesRoot.getFolders();

  while (clientIter.hasNext()) {
    const cf = clientIter.next();
    clientFolders.push(cf.getName());
    const projIter = cf.getFolders();
    while (projIter.hasNext()) projectFolders.push(projIter.next().getName());
  }

  for (let i = 0; i < data.length; i++) {
    try {
      const props = data[i].properties;

      const invoiceUrl = safeGet(props, ['Invoice URL', 'url'], null);
      const createDoc  = safeGet(props, ['Create Invoice Document', 'checkbox'], 0);

      if (invoiceUrl !== null || createDoc !== 1) continue;

      const client      = safeGet(props, ['From', 'rollup', 'array', 0, 'select', 'name'], null);
      const clientCode  = safeGet(props, ['Client Code', 'rollup', 'array', 0, 'select', 'name'], null);
      const projectCode = safeGet(props, ['Project Code', 'rollup', 'array', 0, 'select', 'name'], null);
      const invoiceName = safeGet(props, ['Name', 'title', 0, 'plain_text'], null);
      const dateInvoiced= safeGet(props, ['Date Invoiced', 'date', 'start'], null);
      const invoiceRef  = safeGet(props, ['Invoice Reference', 'formula', 'string'], null);
      const amount      = safeGet(props, ['Amount', 'number'], null);
      const notice      = safeGet(props, ['Days Notice', 'number'], null);
      const address     = safeGet(props, ['Address', 'rollup', 'array', 0, 'rich_text', 0, 'plain_text'], '');
      const taxId       = safeGet(props, ['Tax_ID', 'rollup', 'array', 0, 'rich_text', 0, 'plain_text'], '');
      const idStr       = safeGet(props, ['Id', 'rich_text', 0, 'plain_text'], null);
      const typeCode    = safeGet(props, ['Type Code', 'rich_text', 0, 'plain_text'], null);
      const currency    = safeGet(props, ['Currency', 'select', 'name'], 'GBP');

      const required = [client, clientCode, projectCode, invoiceName, dateInvoiced, invoiceRef, amount, notice];
      if (!required.every(v => v !== null)) {
        const names = ['Client', 'Client Code', 'Project Code', 'Invoice Name', 'Date Invoiced', 'Invoice Ref', 'Amount', 'Notice'];
        const missing = names.filter((_, idx) => required[idx] === null).map(n => ` * ${n}`).join('\n');
        MailApp.sendEmail(
          CONFIG.EMAIL.ADMIN,
          'AppScript error - Invoice for : ' + (invoiceName || '(missing name)'),
          `Invoice could not be created because some Notion properties were empty:\n${missing}`
        );
        continue;
      }

      Logger.log(`Preparing invoice doc: ${invoiceName}, client: ${client}, project: ${projectCode}`);

      const pageId = data[i].id;
      const [propNames, values, rowCounts] = getNotionInvoiceTables(dbId, i);

      // Folders: ensure client/project exist
      let clientFolder = clientFolders.includes(client)
        ? getDriveFolderByName(invoicesRoot, client)
        : invoicesRoot.createFolder(client);
      if (!clientFolders.includes(client)) clientFolders.push(client);

      let projectFolder = projectFolders.includes(projectCode)
        ? getDriveFolderByName(clientFolder, projectCode)
        : clientFolder.createFolder(projectCode);
      if (!projectFolders.includes(projectCode)) projectFolders.push(projectCode);

      const docName = dateInvoiced.replace(/-/g, '').slice(2) + '_' + clientCode + '_' + invoiceName;

      if (getDriveFileByName(projectFolder, docName)) {
        MailApp.sendEmail(
          CONFIG.EMAIL.ADMIN,
          'Invoice for : ' + invoiceName,
          'An Invoice document already exists here: ' + projectFolder.getUrl() +
          ' ,but it is not connected to Notion Income DB. Document name: ' + docName
        );
        continue;
      }

      // Pick template per currency
      const templateId = CONFIG.DOC_TEMPLATES[currency] || CONFIG.DOC_TEMPLATES.GBP;
      const template = DriveApp.getFileById(templateId);
      const copy = template.makeCopy(docName, projectFolder);

      const doc = DocumentApp.openById(copy.getId());
      const body = doc.getBody();
      const symbol = currencySymbol_(currency);

      const vatAmount = amount * CONFIG.INVOICE.VAT_RATE;
      const totalAmount = amount + vatAmount;

      body.replaceText('{{Invoice Reference}}', invoiceRef);
      body.replaceText('{{Date Invoiced}}', dateInvoiced);
      body.replaceText('{{Client}}', client);
      body.replaceText('{{Amount}}', symbol + amount);
      body.replaceText('{{VAT}}', symbol + vatAmount);
      body.replaceText('{{TotalAmount}}', symbol + totalAmount);
      body.replaceText('{{Notice}}', String(notice));
      body.replaceText('{{Currency}}', currency);
      body.replaceText('{{Address}}', address);
      body.replaceText('{{Tax_ID}}', taxId);

      // Scope of Work bullets
      const bulletpoints = ULC_Sheets.flatten(values[0]); // scope table values flat → list of strings
      gdoc_addBulletPointsBelow(doc, 'Scope of Work', bulletpoints);

      // Agreed Fee Structure table
      const tableData = values[1]; // 2nd table
      gdoc_addTableData(doc, tableData, symbol);

      Logger.log('Replaced placeholders with Notion values');

      // Export to PDF and move into same folder
      const pdfBlob = doc.getAs('application/pdf').setName(doc.getName() + '.pdf');
      const pdfFile = DriveApp.createFile(pdfBlob);
      moveFileId(pdfFile.getId(), projectFolder.getId());

      const pdfUrl = pdfFile.getUrl().toString();

      // Write back Invoice URL to Notion
      ULC_NotionAPI.setNotionPropertyValue(pageId, {
        properties: { 'Invoice URL': { url: pdfUrl } }
      });

      MailApp.sendEmail(
        CONFIG.EMAIL.ADMIN,
        'Invoice for : ' + invoiceName,
        `A new Invoice Document was created for client: ${client} and project: ${projectCode}\nLink: ${pdfUrl}`
      );
      Logger.log('Invoice created & emailed: ' + pdfUrl);

      // Currency conversion to GBP can be added here later if needed.

    } catch (err) {
      Logger.log('Error on row ' + i + ': ' + (err && err.stack ? err.stack : err));
      // Consider notifying admin once per run if critical.
    }
  }
}

---

What belongs in a reusable library vs. this doc-specific script

✅ Put these in a reusable library (stable, generic, testable)

• Notion utilities

  • resolveDatabaseIdFromBlock_
  • validateDatabaseId_
  • resolveDatabaseIdsFromPage_ (BFS page/children scanner)
  • A robust pagination/query wrapper (you already have ULC_NotionAPI.notionData—keep and harden).
  • A setProperty wrapper for Notion (ULC_NotionAPI.setNotionPropertyValue).
  • A retrieveDatabase / retrievePage / listBlockChildren trio with strict validation & verbose errors.

• Docs helpers

  • gdoc_addTableData (parameterized for column mapping & alignment)
  • gdoc_addBulletPointsBelow (parameterize font size, color, list type)

• Drive helpers

  • getDriveFolderByName, getDriveFileByName, moveFileId

• Utility functions

  • safeGet (deep getter)
  • currencySymbol_ (or a fuller currency/locale utility)
  • Collection helpers (nonEmptyArray_, chunkers, flatteners if you don’t want to depend on ULC_Sheets)

Rationale: these are agnostic (don’t know about “Invoice”, “Scope”, your workspace), and you’ll reuse them across multiple automations.

🧾 Keep these in the document-specific script

• Business flow/orchestration: createInvoiceDocument
  • It encodes your business rules: when to create, how to name files, folder structure, which fields are required, VAT logic, which templates map to which currency, who to email, what placeholders to update.
• Invoice-specific transforms
  • Any bespoke mapping of Notion properties → doc placeholders.
  • The particular order & layout of the fee table.
  • Any custom validation that’s specific to your Income DB schema.

Rationale: these are bespoke to this template, this DB schema, this Drive hierarchy, and this team’s workflow.

---

Extra polish you might consider

• Retry/backoff for Notion 429s (rate limits).
• Centralized logging helper that prefixes timestamps and context (pageId, databaseId, function).
• Feature flags in CONFIG (e.g., SEND_EMAILS: true/false, WRITE_BACK_TO_NOTION: true/false).
• Unit-ish tests for the pure helpers (e.g., feed synthetic block trees into resolveDatabaseIdsFromPage_).
• Stricter table mappers: for gdoc_addTableData, accept a mapping object so you’re not tied to [qty, amount, unit, description] order.
• Currency conversion service wrapper (cachable daily) once you’re ready.

If you want, I can split the above into two files—ULC_Library.gs (with the helpers) and InvoiceRunner.gs (with createInvoiceDocument)—and include a tiny README on how to version and consume the library.

You

yes, split and create readme

ChatGPT

[Unsupported Content]

ChatGPT

[Unsupported Content]

ChatGPT

Done! I split your code and added a README. You’ll see three sections in the canvas:

• ULC_Library.gs (reusable helpers)
• InvoiceRunner.gs (project-specific flow)
• README.md (setup, usage, troubleshooting, and how to publish as a library)

If you want, I can also:

• turn ULC_Library.gs into an actual Apps Script Library with suggested version tags, or
• parameterize the table-column mapping for gdoc_addTableData so it’s 100% schema-agnostic.

You

Can I manipulate google docs in the same way, but outside the apps script interface? let’s say from a c# app. I DONT want to get into google cloud etc, as its very complicvated. I need simple scripts and api calls,. to read a template doc, make a copy in drive, extract a pdf out of it

ChatGPT

[Unsupported Content]

ChatGPT

[Unsupported Content]

ChatGPT

Short version: you can do it from C#, but if you want to avoid “getting into Google Cloud” entirely, the easiest path is to keep Apps Script as the thing that actually touches Docs/Drive and let your C# app just call the script. If you try to do it directly from C#, you end up doing the same OAuth / API enabling dance that Apps Script is already doing for you. That’s just how Google’s security model works. citeturn0search4turn0search12

Let me lay out the realistic options.

Option 1 (easiest): C# → call Apps Script (web app or Apps Script API)

1. Put your “read template doc → make copy → fill → export PDF → return URL” code in Apps Script (you basically already have it).
2. Deploy that script as:
   • a Web App (doPost(e) / doGet(e)) that returns JSON. citeturn0search3
   • or an API executable and call it via scripts.run from C#. citeturn0search5turn0search11
3. Your C# app does one HTTP request to that URL. Done.

Why this is nice:

• You don’t have to figure out Docs API + Drive API + batchUpdate formatting in C#.
• The script runs as you (or as the script owner), so permissions are already solved.
• You only expose a tiny, friendly endpoint to C#.

What you still need:

• For a private web app you’ll still need to authenticate the C# call (OAuth or service account + domain sharing), but you can also make it “Anyone with the link” if this is a personal tool and you accept that risk.

I’d do this if “I don’t want to mess with Google Cloud” is the priority.

---

Option 2 (more control): C# talks to Drive/Docs directly

This is the “official” way, but it does mean enabling APIs + OAuth. There’s no way around it if C# is the thing calling Google. Here’s the bare minimum:

1. Enable Drive API (to copy docs and export PDF) and optionally Docs API (to edit contents) in your project. citeturn0search0turn0search6turn0search14
2. In C#, install:

   dotnet add package Google.Apis.Drive.v3
   dotnet add package Google.Apis.Docs.v1

3. Do OAuth (user-consent) once → you get tokens → store refresh token.
4. Then you can:
   • Copy template: drive.files.copy
   • (Optional) edit doc: Docs API documents.batchUpdate to replace placeholders
   • Export to PDF: drive.files.export(fileId, "application/pdf") (there’s literally a C# helper for it). citeturn0search8

Pseudo-flow:

// 1. copy template
var copyReq = driveService.Files.Copy(new File { Name = "Invoice 2025-10-30" }, templateId);
var copy = await copyReq.ExecuteAsync();
var newDocId = copy.Id;

// 2. edit with Docs API (replace {{...}}) – optional
var requests = new List<Request> {
    new Request {
        ReplaceAllText = new ReplaceAllTextRequest {
            ContainsText = new SubstringMatchCriteria { Text = "{{Client}}", MatchCase = false },
            ReplaceText = "ACME Ltd"
        }
    }
};
await docsService.Documents.BatchUpdate(new BatchUpdateDocumentRequest { Requests = requests }, newDocId).ExecuteAsync();

// 3. export to PDF
var exportReq = driveService.Files.Export(newDocId, "application/pdf");
var stream = new MemoryStream();
await exportReq.DownloadAsync(stream);
// save stream to disk

That’s exactly what the Drive docs show for exporting a Doc to PDF. citeturn0search0turn0search8turn0search14

👉 The part you said you don’t want (“get into google cloud etc”) is basically steps 1–3 (enable API, create creds, do OAuth). There’s no secret simpler HTTP endpoint that skips auth.

---

Option 3 (halfway): C# calls a published Apps Script web app that itself uses your OAuth

This is just Option 1 with a clearer shape:

• Apps Script code (in your Google account) = already authorized to Docs/Drive.
• You deploy it “Anyone, even anonymous”.
• Your C# app does:

  var resp = await http.PostAsJsonAsync("https://script.google.com/macros/s/AKfycb.../exec",
      new { templateId = "...", name = "...", placeholders = new {...} });

• Script returns { pdfUrl: "...", docId: "..." }.

Zero Google Cloud setup on the C# side. You just need one Google-side setup: deploying the script. citeturn0search3turn0search11

---

So, can you do what you want?

Yes, but:

• From C# directly → you can’t skip Google auth setup.
• If you really want “simple” → let Apps Script do the Google-y parts, and your C# just calls it.

If you tell me which of these you’d rather tolerate:

1. “I’m ok deploying an Apps Script web app, C# just hits a URL”
2. “No, I want pure C#, I’ll do OAuth once” …I can give you the exact code skeleton for that path.