Skip to main content

Google Apps Script GAS: update value, what is the syntax for UNchecking a box

  • September 13, 2022
  • 8 replies
  • 1792 views
M

Greetings from the dystopia of nested backslashes. I hope to save you some trouble even if I myself am not fully free of it.

Many Monday posts exist; however translating them into something executable for Google Apps script has not been obvious, which is unfortunate because Google Script is among the most accessible ways for casual users to leverage the Monday API for small tasks. Getting started is as easy as typing script.new into your browser address bar and following the prompts.

Key insights thus far on Monday / Google Sheets integration:

  1. Across Monday boards, Column IDs vary for the exact same Column Name. No fetchColIDFromName kind of function exists; thus you’ll need to loop all columns and match on the expected name. The code block is not rendering properly in this venue, so please see getColIDFromName here
  2. Google Apps Script expects the word “payload” instead of “body” (see working examples at mondayintegration/monday.gs at main · bridge2ai/mondayintegration · GitHub)
  3. passing in \\"true\\" for checking boxes works fine; however passing in \\"false\\" fails silently along with 13 other permutations of blank or null. 2 additional options trigger fatal errors.
  // all of these fail silently (they do not change state of checkbox to true, but they don't throw errors either)
  var silentFailParameters = [
    null,
    false,
    'false',
    '\\"false\\"',
    '\\\\"false\\\\"',
    '\\\\\\"false\\\\\\"',
    '"{}"',
    '\\"{}\\"',
    '\\\\"{}\\\\"',
    '\\\\\\"{}\\\\\\"',
    '\\\\\\"\\\\\\"'
  ];

  var fatalErrorParameters = [
    "",
    {}
  ];

I’m what am I missing?

thanks in advance,
Julie

8 replies

Matias.Monday
Forum|alt.badge.img
  • Matias.Monday
  • monday.com Team Member
  • September 13, 2022

Hello @McMurry!

Regarding the clearing of a checkbox column, you can use a mutation like this one:

mutation {
  change_multiple_column_values(item_id: 1234567890, board_id: 1122334455, column_values: "{\\"checkbox\\" : null}") {
    id
  }
}

Hope that helps!

Cheers,
Matias

M
  • McMurry
  • Author
  • Participating Frequently
  • September 13, 2022

Hi Matias, Apologies; I wasn’t clear, null is the first permutation I tried
I’m iteratively passing each of the failing values into the method below. Unfortunately, Google apps script won’t work with the raw api syntax; it has to be pretty close to pure javascript.

  function testParameterEncoding(permutation) {
    var query =
      'mutation{change_column_value(' +
      'board_id: ' + Board_id + ",item_id:" + Item_id +
      ',column_id: "checkbox",value:"{\\\\"checked\\\\": ' + permutation + '}"){id}}'

    Logger.log('-----------------------------')
    Logger.log("Testing query with: " + JSON.stringify(permutation));

    runQuery(query);
    let result = runQuery(queryCheckboxState);
    getCheckedStateFromResult(result)
  }

which then runs the query via the method below:

  function runQuery(query) {

    var result = UrlFetchApp.fetch("https://api.monday.com/v2", {
      method: 'post',
      headers: {
        'Content-Type': 'application/json',
        'Authorization': MONDAY_KEY
      },
      payload: JSON.stringify({
        'query': query
      })
    })
    return result;
  }
}

then the output of the iterative execution is:

7:37:29 AM	Info	Started: Tue Sep 13 2022 07:37:29 GMT-0700 (Pacific Daylight Time)
7:37:29 AM	Info	Checking baseline checkbox state
7:37:29 AM	Info	null
7:37:29 AM	Info	-------
7:37:29 AM	Info	-----------------------------
7:37:29 AM	Info	Testing query with: "\\\\\\"true\\\\\\""
7:37:30 AM	Info	{"checked":"true","changed_at":"2022-09-13T14:37:29.801Z"}
7:37:30 AM	Info	-----------------------------
7:37:30 AM	Info	Testing query with: null
7:37:31 AM	Info	{"checked":"true","changed_at":"2022-09-13T14:37:29.801Z"}
7:37:31 AM	Info	-----------------------------
7:37:31 AM	Info	Testing query with: false
7:37:31 AM	Info	{"checked":"true","changed_at":"2022-09-13T14:37:29.801Z"}
7:37:31 AM	Info	-----------------------------
7:37:31 AM	Info	Testing query with: "false"
7:37:32 AM	Info	{"checked":"true","changed_at":"2022-09-13T14:37:29.801Z"}

and so on for the other fails.

    M
    • McMurry
    • Author
    • Participating Frequently
    • September 13, 2022

    It was @ MelissaH who got me partially unstuck via this post; THANKS: Mutating a Date in v2
    I’d be very grateful if it might be possible to comment on how, in Google Apps Script to pass in null to uncheck a box?
    Thanks in advance 🙂

      M
      • MelissaH
      • Leader
      • September 13, 2022

      Julie,

      it’s been several years since I tried to uncheck a box, but here’s how I typically write my Monday API queries:

      1. Tweak the query in the API playground until I get it working there - usually the documentation leaves things out and a raw copy/paste involves some tweaking to get confirmation
      2. Switch to Postman and tweak it again until the JavaScript works the same way the original query did - as you found, there are quirks to the way the Monday API interprets backslashes and quotation marks.
      3. Once I have a working JavaScript query in Postman, it’s usually a pretty simple copy/paste to GAS.
        Matias.Monday
        Forum|alt.badge.img
        • Matias.Monday
        • monday.com Team Member
        • September 14, 2022

        Hello @McMurry!

        Please note that the mutation you are using is change_column_values and what I sent is change_multiple_column_values.

        Also, at least on the GraphQL side, you are using two backslashes to escape outer quotes when only one is needed (not sure if you are using 2 because of some GAS syntax requirement). And no quotes are needed for null:

        column_values: "{\\"checkbox\\" : null}"

        Regarding the syntax required in GAS, I am sorry I can not help there since I have no experience with it.

        Cheers,
        Matias

        M
        • McMurry
        • Author
        • Participating Frequently
        • October 6, 2022

        Thanks, when I get back to this project, I will certainly try. I appreciate your willingness to respond.

          M
          • MelissaH
          • Leader
          • October 6, 2022

          Matias,

          GAS syntax for API requests is diabolical and ever-changing. consider yourself lucky to have no experience in it 😃

            M
            • McMurry
            • Author
            • Participating Frequently
            • October 6, 2022

            Well hey, at least in GAS API, I’ve never seen “null” and “true” being required opposites of each other 😉

              Terms & ConditionsCookie settingsAccessibility statement