Why am I getting this error with Google Apps Scripts?

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)
}

Hello there @ryoma!

I do not have any experience using Google Apps Script but it looks to me like that error is coming from GAS and not from our server. I am guessing from the error message that you are passing a “Header” somewhere that needs to have a different value than “null”, but again, this is just what I can see from what you sent since I do not have experience with it.

Let’s hope someone in the community has worked with it and can give some insights!

Cheers,
Matias

Hey Matias,

Sorry for the late reply! Thank you so much for your help!