syncing Google Calendar>Google Sheets>Smartsheet_API errors_using Apps Script

johnnipearson

Hi Everyone

Does anyone have a procedure or a code I can follow to have Google Apps Script parse data in google sheets over to smartsheet? I have been using this code below but the data still isn't appearing in smartsheet. I know I could be using zapier but I would prefer to use Apps Script so everything is dedicated on Googles backend.

Thanks for any help you can offer or even supply a code I can use. - John

function getDataFromGoogleSheet() {

 try {

  // Replace 'YOUR_SPREADSHEET_ID' with the actual ID of your Google Sheet

  var spreadsheetId = 'YOUR_SPREADSHEET_ID';

  

  // Replace 'Sheet1' with the name of the sheet containing your data

  var sheetName = 'SHEET1';

  

  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);

  var data = sheet.getDataRange().getValues();

  

  // Log the data to ensure it's fetched correctly

  Logger.log(data);

  

  // Return the data

  return data;

 } catch (error) {

  Logger.log("Error in getDataFromGoogleSheet: " + error.toString());

  return []; // Return an empty array if there's an error

 }

}

function sendDataToSmartsheet(data) {

 try {

  // Replace 'YOUR_SMARTSHEET_ACCESS_TOKEN' with your Smartsheet API access token

  var accessToken = 'YOUR_SMARTSHEET_ACCESS_TOKEN';

  

  // Replace 'YOUR_SMARTSHEET_SHEET_ID' with the actual ID of your Smartsheet

  var smartsheetSheetId = 'YOUR_SMARTSHEET_SHEET_ID';

  

  // Construct the API URL

    var apiUrl = 'https://api.smartsheet.com/2.0/sheets/' + smartsheetSheetId + '/rows';

  

  // Log the data to ensure it's not undefined

  Logger.log("Data received: " + JSON.stringify(data));

   

  // Check if data is not undefined and has a length property

  if (data && data.length) {

   // Prepare the data payload for Smartsheet

   var payload = {

    "toBottom": true,

    "cells": []

   };

   

   // Loop through the data and add each row to the payload

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

    var row = data[i];

    var rowData = [];

    

    // Add each cell value to the rowData array

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

     rowData.push({ "columnId": j + 1, "value": row[j] });

    }

    

    // Add the rowData to the cells array in the payload

    payload.cells.push(rowData);

   }

   

   // Set the options for the HTTP request to Smartsheet

   var options = {

    'method': 'post',

    'headers': {

     'Authorization': 'Bearer ' + accessToken,

     'Content-Type': 'application/json',

    },

    'payload': JSON.stringify(payload)

   };

   

   // Make the HTTP request to Smartsheet

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

   

   // Log the response to check for errors

   Logger.log("Response from Smartsheet API: " + response);

  } else {

   Logger.log("Error: Data is undefined or has no length.");

  }

 } catch (error) {

  Logger.log("Error in sendDataToSmartsheet: " + error.toString());

 }

}

function runScript() {

 var data = getDataFromGoogleSheet();

 sendDataToSmartsheet(data);

}

Lee Joramo

Can you provide us with any of the logged responses? Especially the output here:

   // Make the HTTP request to Smartsheet
   var response = UrlFetchApp.fetch(apiUrl, options);
   // Log the response to check for errors
   Logger.log("Response from Smartsheet API: " + response);

Also could your provide the value our your payload

HINT: when you are typing in this forum you can format you text as code with syntax highlighting There is a "Paragraph" symbol to the left of the text you are editing. If you click on this you access some more formatting options including to Quote the text as a Code Block:

johnnipearson

Hi Lee

Thank you for your response. I'm facing a bit of a challenge and could use some guidance. I'm relatively new to coding and still in the process of learning the terminology. When I mention 'the value of my payload,' I'm referring to various sections of data coming from Google Calendar, such as location, start/end times, booking titles, addresses, and other standard information typically associated with events. I need that calendar data onto Smartsheet so I can link some cells together for reporting and operations. Your assistance in navigating this would be greatly appreciated

I have two distinct Google Sheets, both successfully using code to fetch data from Google Calendar to Google Sheets. However, when I attempt to integrate additional code for a connection between Google Calendar, Google Sheets, and Smartsheet, the integration fails to work, despite the execution log indicating 'success.'"

Execution Log.png

I'm trying to set up a process where clients can book times with me on Google Calendar. I've successfully synced that data onto Google Sheets. Now, I want to further sync the Google Sheets data onto a Smartsheet. Despite the Apps Script indicating a 'success' in the run command, the data isn't appearing on the Smartsheet. Any insights on what might be causing this discrepancy would be appreciated

Execution Log 2.png

Something is hindering this process I can't figure out. Below is the snippet/code I am using which executes as "success" but fails to deliver the expected outcome. It is possible I haven't allowed a setting in Smartsheet, though nevertheless the data is now NOT arriving on google sheets either despite executing as "success"

// Sync data from Google Sheet to Smartsheet

function syncDataToSmartsheet() {

try {

// Replace 'YOUR_SHEET_ID' with the actual Google Sheet ID

var sheet = SpreadsheetApp.openById('YOUR GOOGLE SHEET ID').getActiveSheet();

// Get data from Google Sheet

var data = sheet.getDataRange().getValues();

// Replace 'YOUR_SMARTSHEET_ACCESS_TOKEN' with your Smartsheet API access token

var accessToken = 'YOUR SMARTSHEET ACCESS TOKEN';

// Replace 'YOUR_SMARTSHEET_SHEET_ID' with the actual Smartsheet Sheet ID

var smartsheetSheetId = '6553996625923972';

// Construct the URL for the Smartsheet API

var apiUrl = 'https://api.smartsheet.com/2.0/sheets/' + smartsheetSheetId + '/rows';

// Set up the headers for the Smartsheet API request

var headers = {

'Authorization': 'Bearer ' + accessToken,

'Content-Type': 'application/json'

};

// Prepare the payload for the Smartsheet API request

var payload = {

'toTop': true,

'cells': []

};

// Iterate through the data and add each row to the payload

for (var i = 1; i < data.length; i++) {

var row = data[i];

var rowData = {};

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

rowData['columnId'] = j + 1;

rowData['value'] = row[j];

payload['cells'].push(rowData);

}

}

// Make the API request to Smartsheet

var response = UrlFetchApp.fetch(apiUrl, {

'method': 'POST',

'headers': headers,

'payload': JSON.stringify(payload)

});

Logger.log(response.getContentText());

} catch (e) {

Logger.log('Error: ' + e.toString());

}

}

// Trigger the function every minute to check for new events

function triggerSyncEveryMinute() {

// Set up a time-driven trigger to run every minute

ScriptApp.newTrigger('syncDataToSmartsheet')

.timeBased()

.everyMinutes(1)

.create();

}

// Wrap your existing code in a function (optional)

function myFunction() {

// Get the active spreadsheet

var spreadsheet = SpreadsheetApp.openById('1qBTfD0S4R3yYzt_8SkPYWJFJ74bMBLcq0JVrbaW5DP8'); // Replace with your Google Sheets ID

// Specify the sheet name

var sheetName = 'HSS Home Visit 3';

// ... (rest of your existing code for syncing Google Calendar events to Google Sheet)

}

Lee Joramo

johnni, I didn't want to leave you hanging, but I haven't had time to look at this. I will try to in the next day to two. (Also, while I do use the Smartsheet API for both Javascript and Python, I don't anything from within Google, so while I think I will be able help, it maybe limited help)

johnnipearson

Hi Lee

Sorry as well for my late reply. I have been absolutely flat out. 2024 kicked off hard and fast and I had to steady the ship.

No worries at all. I may even just use Zapier I am thinking because it may be easier than reinventing the wheel too much. I would still like to prevent to much reliance on third-party apps.

If you could lightly think about if what I am requiring is possible it would be appreciated otherwise Zapier will be sufficient.

Thanks Lee! Hope all is well with you.