What's the most efficient way to get all attachments from any row in every sheet using Smartsheet API?
Bottom line, is there any way to get a list of attachments without drilling down to the row level having to make 2 calls to get the attachment id and then 1 more call to get the attachment?
I'm trying to write a script that uses Smartsheet's API to get all desired attachments from all/specific sheets. So far I have code that accomplishes the goal but it's super inefficient because it has to go through thousands of rows checking to see if there's an attachment or not. Both lines of code return objects or list of objects containing "Attachments" property, but it's always null.
// get all sheet resources
PaginatedResult<Sheet> sheets = smartsheet.SheetResources.ListSheets(null, null, null);
var includeAttachments = new List<SheetLevelInclusion> { SheetLevelInclusion.ATTACHMENTS };
var sheet = smartsheet.SheetResources.GetSheet(sheetId, includeAttachments, null, null, null, null, null, null);
Each method has a IEnumerable<SheetLevelInclusion> parameter but I'm not completely sure how to leverage it if it's even possible.
I'll provide my entire method to give you more context. I've also included a FilterSheets method that I have yet to implement. I'm also open to Python answers.
private List<SMTPAttachment> GetAttachments(SmartsheetClient smartsheet)
{
var includeAttachments = new List<SheetLevelInclusion> { SheetLevelInclusion.ATTACHMENTS };
// get all sheet resources
PaginatedResult<Sheet> sheets = smartsheet.SheetResources.ListSheets(null, null, null);
// filter out sheets that don't have desired attachments
var filteredSheets = FilterSheets(sheets);
// initalize necessary objects to store data
var attachments = new List<SMTPAttachment>();
// for each sheet in sheets:
foreach (var tempSheet in filteredSheets)
{
var sheetId = (long)tempSheet.Id;
var sheet = smartsheet.SheetResources.GetSheet(sheetId, includeAttachments, null, null, null, null, null, null);
// for each row in sheet.Rows
foreach (Row row in sheet.Rows)
{
long rowId = (long)row.Id;
var paging = new PaginationParameters(true, 1, 1);
var smtpAttachment = new SMTPAttachment();
PaginatedResult<Attachment> updatedAttachment = smartsheet.SheetResources.RowResources.AttachmentResources.ListAttachments(
sheetId,
rowId,
paging
);
if (updatedAttachment.Data.Count != 0)
{
var attachmentInfo = updatedAttachment.Data.FirstOrDefault();
if (attachmentInfo != null)
{
// finally getting the attachment here
var attachment = smartsheet.SheetResources.AttachmentResources.GetAttachment(sheetId, (long)attachmentInfo.Id);
smtpAttachment.Attachment = attachment;
smtpAttachment.Name = attachment.Name;
smtpAttachment.Url = attachment.Url;
smtpAttachment.MimeType = attachment.MimeType;
attachments.Add(smtpAttachment);
Console.WriteLine("Attaching file : " + attachment.Name);
}
}
else
{
Console.WriteLine("No Data for row: " + rowId);
}
}
}
return attachments;
}
private List<Sheet> FilterSheets(PaginatedResult<Sheet> sheets)
{
/// TODO: implement filter code
var filteredSheets = sheets.Data.ToList().Where(sheet => sheet.Id == sheet.Id).ToList();
return filteredSheets;
}
UPDATE:
My original answer (below) assumes that you're wanting other sheet-related data in addition to information about the attachments. If that IS your scenario, then using a single Get Sheet API request is the most efficient way of doing that, as it'll allow you to use a single API call to retrieve all sheet-related data AND data about attachments within the sheet.
However, if you're only wanting data about attachments within the specified sheet, you might consider instead using the List Attachment operation. This operation returns information about all attachments that exist within the sheet, regardless of location (i.e., sheet attachments, row attachments, discussion attachments at the sheet level, discussion attachments at the row level). Using the List Attachment operation (if you're only wanting attachment-related data and don't need any other sheet data) is more efficient than using the Get Sheet operation to retrieve attachment data, as the response will be much smaller and the code you need to write to process the response much simpler. Here's some sample code:
SmartsheetClient smartsheet = new SmartsheetBuilder()
.SetAccessToken("ADD_YOUR_ACCESS_TOKEN_HERE")
.Build();
var sheetId = (long) 3932034054809476;
// Omit pagination parameters
PaginatedResult<Attachment> attachments = smartsheet.SheetResources.AttachmentResources.ListAttachments(sheetId, null);
Console.WriteLine("Number of attachments found = " + attachments.TotalCount);
foreach(var attachment in attachments.Data) {
Console.WriteLine("Attachment name | ID | location: " + attachment.Name + " | " + attachment.Id + " | " + attachment.ParentType);
}
When run against the sheet I've described in the original answer (below), this code produces the following console output:
Please note that regardless of which API method you use to retrieve attachment info (Get Sheet or List Attachments), the API response will only contain a subset of metadata for each attachment. For each attachment in a Get Sheet or List Attachments response, you'll need to issue a Get Attachment request to retrieve the full set of metadata about the attachment -- including the url property that you can use to access the file itself.
ORIGINAL ANSWER:
It's possible to get info about all attachments contained within a sheet by issuing a single Get Sheet request (with the appropriate SheetLevelInclusion values specified) -- but you'll need to evaluate 4 separate parts of the response to identify all attachments in the sheet. This is because there are 4 different places where an attachment can be added:
- at the SHEET level
- within a DISCUSSION at the SHEET level
- at the ROW level
- within a DISCUSSION at the ROW level
For example, consider a sheet in Smartsheet that contains the 6 attachments listed in the following Smartsheet screenshot, where:
file6.txtis attached to a DISCUSSION at the SHEET levelfile5.txtis attached at the SHEET levelfile4.txtis attached to a DISCUSSION at the ROW level (on row #1)file3.txtandfile2.txtare attached at the ROW level (on row #4)file1.txtis attached at the ROW level (on row #1)
The JSON response to a Get Sheet request for this sheet would look like the following (edited for brevity, to only show relevant data within the response):
{
"id": 3932034054809476,
...
"columns": [
...
],
"rows": [
{
"id": 6933706290423684,
"rowNumber": 1,
...
"discussions": [
{
"id": 5173128113219460,
"title": "This is a discussion!",
"commentCount": 1,
"commentAttachments": [
{
"id": 1958653853755268,
"name": "file4.txt",
"attachmentType": "FILE",
...
}
],
...
}
],
"attachments": [
{
"id": 1061849918400388,
"name": "file1.txt",
"attachmentType": "FILE",
...
}
]
},
{
"id": 1304206756210564,
"rowNumber": 2,
...
},
{
"id": 770536852088708,
"rowNumber": 3,
...
},
{
"id": 7046650170566532,
"rowNumber": 4,
...
"attachments": [
{
"id": 7092080722634628,
"name": "file3.txt",
"attachmentType": "FILE",
...
},
{
"id": 2426000334972804,
"name": "file2.txt",
"attachmentType": "FILE",
...
]
}
],
"discussions": [
{
"id": 4967439377950596,
"title": "This is a comment on the sheet level.",
"commentCount": 1,
"commentAttachments": [
{
"id": 976830369687428,
"name": "file6.txt",
"attachmentType": "FILE",
...
}
],
...
}
],
"attachments": [
{
"id": 135383999375236,
"name": "file5.txt",
"attachmentType": "FILE",
...
}
]
}
This JSON response shows the attachments present at the various locations described previously.
The following C# code issues a Get Sheet request (with the appropriate SheetLevelInclusion values specified) to retrieve the specified sheet (including info about all attachments present at any location within the sheet). It then looks in each of the 4 locations and writes output to the console when it finds an attachment.
SmartsheetClient smartsheet = new SmartsheetBuilder()
.SetAccessToken("ADD_YOUR_TOKEN_STRING_HERE")
.Build();
var sheetId = (long) 3932034054809476;
// Sheet inclusions must specify attachments AND discussions in order to get all attachments present within the sheet
// (i.e., attachments present on: SHEET object, ROW objects, DISCUSSION objects at the SHEET level, DISCUSSION objects at the ROW LEVEL)
var sheetLevelInclusion = new List<SheetLevelInclusion> { SheetLevelInclusion.ATTACHMENTS, SheetLevelInclusion.DISCUSSIONS };
Console.WriteLine("Loading sheet id: " + sheetId);
// Get the sheet.
var sheet = smartsheet.SheetResources.GetSheet(sheetId, sheetLevelInclusion, null, null, null, null, null, null);
Console.WriteLine("Loaded " + sheet.Rows.Count + " rows from sheet: " + sheet.Name);
Console.WriteLine("---------");
// 1- Attachments that exist on the SHEET object
if (sheet.Attachments != null) {
// iterate through all attachments found on SHEET object
foreach (var attachment in sheet.Attachments) {
Console.WriteLine("Attachment found (at SHEET-level): " + attachment.Name + " (attachment ID = " + attachment.Id + ")");
// Add logic to process attachment here.
}
}
Console.WriteLine("---------");
// 2- Attachments that exist on the DISCUSSION objects at the SHEET level
if (sheet.Discussions != null) {
// iterate through all discussion objects (at the sheet level), looking for attachments on each discussion.
foreach (var discussion in sheet.Discussions) {
if (discussion.CommentAttachments != null) {
foreach (var attachment in discussion.CommentAttachments) {
Console.WriteLine("Attachment found (on SHEET-level Discussion): " + attachment.Name + " (attachment ID = " + attachment.Id + ")");
// Add logic to process attachment here.
}
}
}
}
Console.WriteLine("---------");
// 3- Attachments that exist on ROW objects
foreach (var row in sheet.Rows) {
if (row.Attachments != null) {
// process attachments on the current row
foreach (var attachment in row.Attachments) {
Console.WriteLine("Attachment found (at ROW-level) on Row # " + row.RowNumber + ": " + attachment.Name + " (attachment ID = " + attachment.Id + ")");
// Add logic to process attachment here.
}
}
// 4- Attachments that exist on DISCUSSION objects at the ROW level
if (row.Discussions != null) {
// iterate through all discussion objects (on the current row), looking for attachments on each discussion.
foreach (var discussion in row.Discussions) {
if (discussion.CommentAttachments != null) {
foreach (var attachment in discussion.CommentAttachments) {
Console.WriteLine("Attachment found (on ROW-level Discussion) in Row # " + row.RowNumber + ": " + attachment.Name + " (attachment ID = " + attachment.Id + ")");
// Add logic to process attachment here.
}
}
}
}
}
Console.WriteLine("---------");
When run against the sheet I've described previously, this code produces the following console output:
Hope this helps!