Pulling Specific Columns in a Group Data into Google Sheet

monday Apps & Developers Feature Requests - API and Apps Framework
, , , , , ,
1

Hi everyone, so I am trying to pull specific columns for all items in a group using API. I need only items and not the subitems. I tried using this script on apps script but keep falling into error. I am a newbie and need guide:

function importBoardItemsByGroup() {
const apiKey = “YOUR_MONDAY_API_KEY”;
const boardId = 3745952099;
const groupId = “topics”; // The confirmed group ID for “Pre-Event”

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet1”);
sheet.clear();

// Query to get all items on the board and filter by group ID
const query = { boards(ids: ${boardId}) { items { group { id } name column_values { title text } } } };

const options = {
method: “post”,
contentType: “application/json”,
headers: { Authorization: apiKey },
payload: JSON.stringify({ query: query })
};

const response = UrlFetchApp.fetch(“https://api.monday.com/v2”, options);
const data = JSON.parse(response.getContentText());
Logger.log(data); // Verify the structure of the returned data

const headers = [“Item”, “Workshop Date”, “Ready 2WIA”, “Date Status”, “Type (Facilitator Location)”, “Facilitator”, “Firm / Company/Group”, “Topic”, “Fee”, “Contract”, “Numbers”];

sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

if (!data.data || !data.data.boards || data.data.boards[0].items.length === 0) {
Logger.log(“No data found or items are empty.”);
return;
}

// Filter items by the desired group ID
const rows = data.data.boards[0].items
.filter(item => item.group.id === groupId)
.map(item => {
const row = ;
headers.forEach(header => {
const column = item.column_values.find(col => col.title === header);
row.push(column ? column.text : “”);
});
return row;
});

if (rows.length > 0) {
sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
} else {
Logger.log(“No data found for the specified group.”);
}
}

2

Hello there @Zubair2icy and welcome to the community!

I hope you like it here :muscle:

In this case, you can not query the items directly from the boards. You need to use the “items_page” query as explained here.

You also need to add the “columns” object here to get the column’s data.

This would be an example:

{
  boards(ids: 7822773244) {
    items_page {
      items {
        group {
          id
        }
        name
        column_values {
          column {
            title
            id
            type
          }
          text
        }
      }
    }
  }
}

Let me know if you have any other questions!

Cheers,
Matias

1 Like
3

Thank you. Worked. What if I want to pull the subitems for all the columns in a group. Is that possible?

4

Hello again @Zubair2icy,

Do you mean you want to get all the subitems for all the “items” inside a group?

If so, you could use something like this:

{
  boards(ids: 7291403118) {
    groups(ids: "topics") {
      items_page {
        items {
          name
          column_values {
            column {
              title
              id
              type
            }
            text
          }
          subitems {
            name
            id
            column_values {
              value
              text
            }
          }
        }
      }
    }
  }
}

But I do recommend getting all the items with their IDs in one query, and then using another one to check the subitems (to avoid rate limits issues)