Script to get data from Google Sheet to Smartsheet

SeanPM

I'm trying pull data from either s csv or google sheet. Can anyone explain to me what is wrong with my script below? No matter how hard I try I get the error:

"Unable to parse request. The following error occurred: Field \"null\" was of unexpected type.",

function copyDataFromGoogleSheetToSmartsheet() {

  var smartsheetAccessToken = "xxxx";

  var googleSheetId = "bbbb";

  var googleSheet = SpreadsheetApp.openById(googleSheetId);

  var googleSheetData = googleSheet.getSheetByName("Sheet2").getDataRange().getValues();

 

  var smartsheetSheetId = "1111";

  var columnIds = [

    659750018568068, // Column ID for URL

    5163349645938564, // Column ID for Last Updated

    2911549832253316 // Column ID for Post ID

  ];

  var payload = {

    toBottom: true,

    cells: []

  };

  for (var i = 0; i < googleSheetData.length; i++) {

    var rowData = googleSheetData[i];

    var cellArray = [];

    for (var j = 0; j < columnIds.length; j++) {

      var value = rowData[j];

      cellArray.push({

        columnId: columnIds[j],

        value: value

      });

    }

    payload.cells.push(cellArray);

  }

  var headers = {

    Authorization: "Bearer " + smartsheetAccessToken,

    "Content-Type": "application/json"

  };

  var url = "https://api.smartsheet.com/2.0/sheets/" + smartsheetSheetId + "/rows";

  var options = {

    method: "POST",

    headers: headers,

    payload: JSON.stringify(payload)

  };

  Logger.log("Payload: " + JSON.stringify(payload));

  var response = UrlFetchApp.fetch(url, options);

  Logger.log("Response Code: " + response.getResponseCode());

  Logger.log("Response Content: " + response.getContentText());

}

Might also be worth mentioning that when I run a very simple script to pull Column IDs, I get an error that says:

"message" : "Your Access Token is invalid."

I know that my access token is valid AND it actually returns the data before the error. Very odd.

Any help is appreciated.

Genevieve P.

Hi @SeanPM

Are you using Google Apps Script? I'm not very familiar with Google Apps Script, however my guess is that the error is likely related to the structure of the payload you're constructing for the API request.

Try this structure instead:

var rows = [];

for (var i = 0; i < googleSheetData.length; i++) {
  var rowData = googleSheetData[i];
  var rowCells = [];

  for (var j = 0; j < columnIds.length; j++) {
    var value = rowData[j];

    rowCells.push({
      columnId: columnIds[j],
      value: value
    });
  }

  rows.push({
    toBottom: true,
    cells: rowCells
  });
}

var payload = {
  rows: rows
};

Key Differences:

1. Payload Structure:
   • Your code creates a single payload object with a toBottom property and an empty cells array.
   • The modified code creates an array of rows, where each row contains a toBottom property and an array of cells.
2. Looping and Adding Rows:
   • In your code, you directly push cellArray into payload.cells within the inner loop. This creates a flat list of cells.
   • In the modified code, each row's cells are collected in the rowCells array, and then a complete row (with toBottom and cells) is pushed into the rows array for each iteration of the outer loop.

If this doesn't work for you or isn't correct, I would suggest posting in the StackOverflow Community to chat with other developers.

Cheers,

Genevieve