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

}

Answers

  • Lee Joramo
    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
    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.'"


    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

    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
    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
    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.