Google Sheets + Monday.com API - Change column value

Question

I managed to connect the Monday.com API with google sheets using Scripts and create a new item in my board, however, I can only insert the item name, how can I insert a value in my column.

Code example:

function Create_Line() 
{
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("tests");
  var values = sh.getDataRange().getValues();
  var mondayAPIkey = "API_KEY"
  for(var i=1;i<values.length;i++)
  {
    if(values[i][7]=="")
    { 
      
      var query = "mutation($board:Int!, $name:String!,$colvals:JSON!){create_item(board_id:$board, item_name:$name, column_values:$colvals){id}}";
      var variables = {
                        "board" : "board_id",
                        "name" : values[i][0], //where column A has the name I want for the item
                        "colvals": JSON.stringify({ "column_id": "Coluna 1", "value": "Done"}) //calling the now formatted date variable
                       
                       };
      var pulseID = JSON.parse(makeAPICall(mondayAPIkey, query, variables)).data.create_item.id;
      sh.getRange(i+1, 8).setValue(pulseID)
    }
  }
}

Sample Spreadsheet

EDIT: To explain the question in a better way, the result that I'm looking for is this:

The script runs through the entire google sheets list and creates the lines on Monday.com, the problem is that I can not fill the column value

In order to correctly understand about your question, can you provide the sample value returned from `makeAPICall(mondayAPIkey, query, variables)` and sample output situation you expect? And, unfortunately, I cannot understand about `Row1` and `value` in your provided image. I apologize for this. — Tanaike, Oct 15 '20 at 22:51
So the picture that you have shared and the code does not correspond. You are setting a value at column eight not *A*, also where is the "value". Instead of describing your current outcome, try to describe the variables you have from the Looger and the expected outcome, so we can have a chance to help you. — Raserhin, Oct 16 '20 at 10:21

score 0 · Answer 1 · edited Oct 27 '20 at 20:00

The column values argument you're passing to the monday.com API is in the wrong format. I cannot find your column ID in your original post, so I'm going to assume it's 'coluna_1'.

Try changing your variables to this:

var variables = {
  "board" : YOUR_BOARD_ID,
  "name" : values[i][0],
  "colvals" : JSON.stringify({"coluna_1" : "Done"})
}

According to the official monday API Quickstart:

Our GraphQL schema defines a set of column values as a JSON string (key-value pairs). The keys of the column_values object must be column IDs, and the values must be structured depending on the type of the column.

You can find the column ID by enabling developer mode, as described in this article.

The value depends on the column type in monday.com. You can see a list of columns supported by the monday.com API here: API Documentation

Google Sheets + Monday.com API - Change column value

1 Answers1