syncing Google Calendar>Google Sheets>Smartsheet_API errors_using Apps Script

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

}

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.