unction comments() { const apiKey = 'API KEY'; // Your Smartsheet API token const sheetId = 'SHEET ID'; // Your Smartsheet sheet ID
const baseOptions = { method: 'GET', headers: { 'Authorization': 'Bearer ' + apiKey }, muteHttpExceptions: true // Allows us to inspect error responses };
let allComments = []; // Array to store all retrieved comments
// --- Step 1: Get Sheet-Level Discussions (if any) --- // This is the original endpoint you were trying. It might return 404 if no sheet-level discussions exist. const sheetDiscussionsUrl = `https://api.smartsheet.com/2.0/sheets/${sheetId}/discussions?include=comments`; try { const sheetDiscussionsResp = UrlFetchApp.fetch(sheetDiscussionsUrl, baseOptions); if (sheetDiscussionsResp.getResponseCode() >= 200 && sheetDiscussionsResp.getResponseCode() < 300) { const sheetDiscussionsData = JSON.parse(sheetDiscussionsResp.getContentText()); if (sheetDiscussionsData && sheetDiscussionsData.data) { // Add all discussions and their comments to our collection sheetDiscussionsData.data.forEach(discussion => { if (discussion.comments) { allComments = allComments.concat(discussion.comments); } }); console.log(`Found ${sheetDiscussionsData.data.length} sheet-level discussions.`); } } else { console.warn(`No sheet-level discussions found or API returned an error for /discussions: Status ${sheetDiscussionsResp.getResponseCode()}, Details: ${sheetDiscussionsResp.getContentText()}`); } } catch (e) { console.error(`Error fetching sheet-level discussions: ${e.message}`); }
// --- Step 2: Get Sheet Data to Iterate Through Rows --- // This part is similar to your working exportSmartsheetDataToGoogleSheet function const sheetDataUrl = `https://api.smartsheet.com/2.0/sheets/${sheetId}`; let sheetDataResp; let sheetData;
try { sheetDataResp = UrlFetchApp.fetch(sheetDataUrl, baseOptions); if (sheetDataResp.getResponseCode() >= 200 && sheetDataResp.getResponseCode() < 300) { sheetData = JSON.parse(sheetDataResp.getContentText()); console.log(`Successfully fetched sheet data for sheet ID: ${sheetId}`); } else { console.error(`Failed to fetch sheet data. Status: ${sheetDataResp.getResponseCode()}, Details: ${sheetDataResp.getContentText()}`); throw new Error('Failed to fetch sheet data.'); } } catch (e) { console.error(`An error occurred while fetching sheet data: ${e.message}`); return; // Exit if we can't get sheet data }
// --- Step 3: Iterate through rows and fetch row-level discussions --- if (sheetData && sheetData.rows) { console.log(`Processing ${sheetData.rows.length} rows for comments...`); for (const row of sheetData.rows) { const rowId = row.id; const rowDiscussionsUrl = `https://api.smartsheet.com/2.0/sheets/${sheetId}/rows/${rowId}/discussions?include=comments`;
try { const rowDiscussionsResp = UrlFetchApp.fetch(rowDiscussionsUrl, baseOptions); if (rowDiscussionsResp.getResponseCode() >= 200 && rowDiscussionsResp.getResponseCode() < 300) { const rowDiscussionsData = JSON.parse(rowDiscussionsResp.getContentText()); if (rowDiscussionsData && rowDiscussionsData.data) { rowDiscussionsData.data.forEach(discussion => { if (discussion.comments) { // Add row number and row ID to each comment for context discussion.comments.forEach(comment => { comment.rowId = rowId; comment.rowNumber = row.rowNumber; }); allComments = allComments.concat(discussion.comments); } }); console.log(`Found ${rowDiscussionsData.data.length} discussions for row ID ${rowId} (Row ${row.rowNumber}).`); } } else { // Log a warning if no discussions for a specific row, but don't stop execution console.warn(`No discussions found for row ID ${rowId} (Row ${row.rowNumber}) or API returned an error: Status ${rowDiscussionsResp.getResponseCode()}, Details: ${rowDiscussionsResp.getContentText()}`); } } catch (e) { console.error(`Error fetching discussions for row ID ${rowId}: ${e.message}`); } } } else { console.log('No rows found in the sheet to process for comments.'); }
// --- Step 4: Output all collected comments --- if (allComments.length > 0) { console.log(`Total comments retrieved: ${allComments.length}`); // You can now process 'allComments' array. // For demonstration, let's log them in a more readable format. allComments.forEach((comment, index) => { console.log(`--- Comment ${index + 1} ---`); console.log(`Row ID: ${comment.rowId || 'N/A'}, Row Number: ${comment.rowNumber || 'N/A'}`); console.log(`Comment ID: ${comment.id}`); console.log(`Discussion ID: ${comment.discussionId}`); console.log(`Created By: ${comment.createdBy ? comment.createdBy.name : 'Unknown'} (${comment.createdBy ? comment.createdBy.email : 'N/A'})`); console.log(`Created At: ${comment.createdAt}`); console.log(`Text: ${comment.text}`); if (comment.attachments && comment.attachments.length > 0) { console.log(`Attachments: ${comment.attachments.map(att => att.name).join(', ')}`); } console.log('--------------------'); });
// Optionally, write these comments to a Google Sheet writeCommentsToGoogleSheet(allComments, sheetId);
} else { console.log('No comments (sheet-level or row-level) found for this sheet.'); }}
/** * Writes the collected comments to a new or existing Google Sheet tab. * @param {Array<Object>} comments - An array of comment objects. * @param {string} sheetId - The Smartsheet ID for context in the sheet name. */function writeCommentsToGoogleSheet(comments, smartsheetSheetId) { const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheetName = `Smartsheet Comments - ${smartsheetSheetId}`; const sheet = ss.getSheetByName(sheetName) || ss.insertSheet(sheetName);
sheet.clearContents();
const headers = [ 'Comment ID', 'Discussion ID', 'Row ID', // Added for row-level context 'Row Number', // Added for row-level context 'Created By Name', 'Created By Email', 'Created At', 'Modified At', 'Comment Text', 'Attachments' ]; sheet.appendRow(headers);
const rowsData = comments.map(comment => { const attachments = comment.attachments ? comment.attachments.map(att => att.name).join(', ') : ''; return [ comment.id, comment.discussionId, comment.rowId || '', // Use empty string if not available (for sheet-level comments) comment.rowNumber || '', // Use empty string if not available (for sheet-level comments) comment.createdBy ? comment.createdBy.name : '', comment.createdBy ? comment.createdBy.email : '', comment.createdAt, comment.modifiedAt, comment.text, attachments ]; });
if (rowsData.length > 0) { sheet.getRange(2, 1, rowsData.length, headers.length).setValues(rowsData); }
Logger.log(`Successfully exported ${comments.length} comments to Google Sheet "${sheetName}".`);}
Error I'm getting : No discussions found for row ID 2326838766079876 (Row 34) or API returned an error: Status 404, Details: { "errorCode" : 1006, "message" : "Not Found", "refId" : "p3yw86"}
