Hi all,
I am trying to get column IDs on GAS (google apps script) with the code below. But I am getting an error with below.
Could you please help me out? Thank you!
function getBoardDataClicked() {
// This function is run when the “Get Board Data” button is clicked
const boardId = getBoardId()
// Get board-specific group data from Monday.com and set it to sheet:
const boardName = getAndSetData(boardId, ‘groups’, ‘A6’)
// Get board-specific column data from Monday.com and set it to sheet:
getAndSetData(boardId, ‘columns’, ‘D6’)
// Set board name to sheet:
setboardName(boardName)
// Redetermine column widths:
autoResizeColumns()
}
function getAndSetData(boardId, type, startCellAddress) {
// This function gets board-specific data from Monday.com and set it to sheet.
// The type of the retrieved data is specified by the “type” parameter.
// “startCellAddress” determines the location in which the data is to be pasted on the sheet.
// Make HTTP request:
const resData = getData(boardId, type)
// Set the data on the sheet:
setData(resData.items, startCellAddress)
// Return the board name:
return resData.boardName
}
function getData(boardId, type) {
// This function gets the specified data from Monday.com.
// Get request payload, which in this case is the GraphQL query:
const payload = getPayload(boardId, type)
// make the HTTP request:
const resData = makeAPIrequest(payload)
// Return the relevant data as an object:
return {
boardName: resData[‘boards’][0].name,
items: resData[‘boards’][0][type]
}
}
function makeAPIrequest(payload) {
// This function makes a HTTP request to Monday
const url = ‘https://api.monday.com/v2’
// Get api key from script variables:
const apiKey = getEnvVar(‘MONDAY_API_KEY’)
const options = {
‘method’: ‘POST’,
‘payload’ : JSON.stringify(payload),
‘headers’: {
‘Content-Type’: ‘application/json’,
‘Authorization’ : apiKey
}
}
const res = UrlFetchApp.fetch(url, options)
// Return response prsed as JSON:
return JSON.parse(res).data
}
function getPayload(boardId, type) {
// This function creates the GraphQL query used in the HTTP request
return {
query: { boards(ids: ${boardId}) { name ${type} { id title } } }
}
}
function getBoardId() {
// This function retrieves the board ID defined by the user:
return SpreadsheetApp.getActive().getSheetByName(‘Get Monday Data’).getRange(‘B1’).getValue()
}
function setData(items, startCellAddress) {
// This function sets the retrieved data on the sheet. The location is determined by “startCellAddress” parameter
const sh = SpreadsheetApp.getActive().getSheetByName(‘Get Monday Data’)
sh.getRange(startCellAddress).offset(0, 0, getLastRow(), 2).clear()
const startCell = sh.getRange(startCellAddress)
// Add each of the retrieved data points onto the sheet as (title, id) pairs:
for (let i=0;i<items.length;i++) {
startCell.offset(i, 0).setValue(items[i].title)
startCell.offset(i, 1).setValue(items[i].id)
}
}
function setboardName(boardName) {
// Sets the board name on the sheet.
SpreadsheetApp
.getActive()
.getSheetByName(‘Get Monday Data’)
.getRange(‘D1’)
.setValue(boardName)
}
function getLastRow() {
// Returns the last populated row of the “Get Monday Data” sheet:
return SpreadsheetApp.getActive().getSheetByName(‘Get Monday Data’).getLastRow()
}
function autoResizeColumns() {
// Automatically resizes certain column widths based on their content.
const sh = SpreadsheetApp.getActiveSheet()
sh.autoResizeColumns(1, 2)
sh.autoResizeColumns(4, 2)
}
function setEnvVar(key, value) {
// Sets script-level environment variables.
const scriptProperties = PropertiesService.getScriptProperties()
scriptProperties.setProperty(key, value)
}
function getEnvVar(key) {
// Gets script-level environment variables.
return PropertiesService.getScriptProperties().getProperty(key)
}