private static void AddRowsWithPartialSuccess(SmartsheetClient smartsheet, long sheetId, Cell[] cellsToAdd)
{
Row row0 = new Row.AddRowBuilder(true, null, null, null, null).SetCells(cellsToAdd).Build();
Row row1 = new Row.AddRowBuilder(true, null, null, null, null).SetCells(cellsToAdd).Build();
BulkItemRowResult rows = smartsheet.SheetResources.RowResources.AddRowsAllowPartialSuccess(sheetId, new Row[] { row0, row1 });
Assert.IsTrue(rows.Result.Count == 2);
}
private void TestCreateUpdateRequest()
{
Sheet sheet = smartsheet.SheetResources.CreateSheet(CreateSheetObject());
PaginationParameters pagination = new PaginationParameters(true, null, null);
PaginatedResult <Column> columns = smartsheet.SheetResources.ColumnResources.ListColumns(sheet.Id.Value, new List <ColumnInclusion> {
ColumnInclusion.FILTERS
}, pagination);
Column addedColumn1 = columns.Data[0];
Column addedColumn2 = columns.Data[1];
List <Cell> cellsA = new List <Cell> {
new Cell.AddCellBuilder(addedColumn1.Id.Value, true).Build(),
new Cell.AddCellBuilder(addedColumn2.Id.Value, "new status").Build()
};
Row rowA = new Row.AddRowBuilder(null, true, null, null, null).SetCells(cellsA).Build();
List <Cell> cellsB = new List <Cell> {
new Cell.AddCellBuilder(addedColumn1.Id.Value, true).Build(),
new Cell.AddCellBuilder(addedColumn2.Id.Value, "new status").Build()
};
Row rowB = new Row.AddRowBuilder(null, true, null, null, null).SetCells(cellsB).Build();
IList <Row> newRows = smartsheet.SheetResources.RowResources.AddRows(sheet.Id.Value, new Row[] { rowA, rowB });
List <Recipient> recipients = new List <Recipient>();
Recipient recipient = new Recipient();
recipient.Email = "*****@*****.**";
recipients.Add(recipient);
UpdateRequest updateRequest = new UpdateRequest();
updateRequest.SendTo = recipients;
updateRequest.Subject = "some subject";
updateRequest.Message = "some message";
updateRequest.CcMe = false;
updateRequest.RowIds = new List <long> {
newRows[0].Id.Value
};
updateRequest.ColumnIds = new List <long> {
addedColumn2.Id.Value
};
updateRequest.IncludeAttachments = false;
updateRequest.IncludeDiscussions = false;
smartsheet.SheetResources.UpdateRequestResources.CreateUpdateRequest(sheet.Id.Value, updateRequest);
DeleteSheet(sheet.Id.Value);
}
public Sheet CreateSheet()
{
Sheet sheet = smartsheet.SheetResources.CreateSheet(CreateSheetObject());
Cell cellA = new Cell.AddCellBuilder(sheet.Columns[1].Id.Value, null).SetValue("A").SetStrict(false).Build();
Cell cellB = new Cell.AddCellBuilder(sheet.Columns[1].Id.Value, null).SetValue("B").SetStrict(false).Build();
Cell cellC = new Cell.AddCellBuilder(sheet.Columns[1].Id.Value, null).SetValue("C").SetStrict(false).Build();
Row rowA = new Row.AddRowBuilder(true, null, null, null, null).SetCells(new Cell[] { cellA }).Build();
Row rowB = new Row.AddRowBuilder(true, null, null, null, null).SetCells(new Cell[] { cellB }).Build();
Row rowC = new Row.AddRowBuilder(true, null, null, null, null).SetCells(new Cell[] { cellC }).Build();
sheet.Rows = smartsheet.SheetResources.RowResources.AddRows(sheet.Id.Value, new Row[] { rowA, rowB, rowC });
return(sheet);
}
private static IList<long> AddRows(SmartsheetClient smartsheet, long sheetId, long columnId, Cell[] cellsToAdd)
{
Row row1 = new Row.AddRowBuilder(true, null, null, null, null).SetCells(cellsToAdd).Build();
Row row2 = new Row.AddRowBuilder(true, null, null, null, null).SetCells(cellsToAdd).Build();
Row row3 = new Row.AddRowBuilder(true, null, null, null, null).SetCells(cellsToAdd).Build();
IList<Row> rows = smartsheet.SheetResources.RowResources.AddRows(sheetId, new Row[] { row1, row2, row3 });
IList<long> rowIds = new List<long>();
foreach (Row row in rows)
{
rowIds.Add(row.Id.Value);
}
return rowIds;
}
private static IList <long> AddRows(SmartsheetClient smartsheet, long sheetId, long columnId, Cell[] cellsToAdd)
{
Row row1 = new Row.AddRowBuilder(true, null, null, null, null).SetCells(cellsToAdd).Build();
Row row2 = new Row.AddRowBuilder(true, null, null, null, null).SetCells(cellsToAdd).Build();
Row row3 = new Row.AddRowBuilder(true, null, null, null, null).SetCells(cellsToAdd).Build();
IList <Row> rows = smartsheet.SheetResources.RowResources.AddRows(sheetId, new Row[] { row1, row2, row3 });
IList <long> rowIds = new List <long>();
foreach (Row row in rows)
{
rowIds.Add(row.Id.Value);
}
return(rowIds);
}
public void TestDiscussionResources()
{
SmartsheetClient smartsheet = new SmartsheetBuilder().SetMaxRetryTimeout(30000).Build();
long sheetId = CreateSheet(smartsheet);
Discussion discussionToCreate = new Discussion.CreateDiscussionBuilder("A discussion", new Comment.AddCommentBuilder("a comment").Build()).Build();
Discussion createdDiscussion = smartsheet.SheetResources.DiscussionResources.CreateDiscussion(sheetId, discussionToCreate);
long createdDiscussionId = createdDiscussion.Id.Value;
string path = "../../../IntegrationTestSDK/TestFile.txt";
Discussion createdDiscussionWithFile = smartsheet.SheetResources.DiscussionResources.CreateDiscussionWithAttachment(sheetId, discussionToCreate, path, null);
Assert.IsTrue(createdDiscussionWithFile.Comments[0].Attachments[0].Name == "TestFile.txt");
PaginatedResult <Discussion> discussions = smartsheet.SheetResources.DiscussionResources.ListDiscussions(sheetId, new DiscussionInclusion[] { DiscussionInclusion.COMMENTS, DiscussionInclusion.ATTACHMENTS }, null);
Assert.IsTrue(discussions.TotalCount == 2);
Assert.IsTrue(discussions.Data.Count == 2);
Assert.IsTrue(discussions.Data[0].Id.Value == createdDiscussion.Id.Value || discussions.Data[0].Id.Value == createdDiscussionWithFile.Id.Value);
Assert.IsTrue(discussions.Data[1].Id.Value == createdDiscussion.Id.Value || discussions.Data[1].Id.Value == createdDiscussionWithFile.Id.Value);
Discussion getDiscussionWithFile = smartsheet.SheetResources.DiscussionResources.GetDiscussion(sheetId, createdDiscussionWithFile.Id.Value);
Assert.IsTrue(getDiscussionWithFile.Title == "A discussion");
Assert.IsTrue(getDiscussionWithFile.Comments.Count == 1);
Assert.IsTrue(getDiscussionWithFile.Comments[0].Attachments.Count == 1);
Assert.IsTrue(getDiscussionWithFile.Comments[0].Attachments[0].Name == "TestFile.txt");
Row row = new Row.AddRowBuilder(true, null, null, null, null).Build();
IList <Row> rows = smartsheet.SheetResources.RowResources.AddRows(sheetId, new Row[] { row });
Assert.IsTrue(rows.Count == 1);
Assert.IsTrue(rows[0].Id.HasValue);
long rowId = rows[0].Id.Value;
Comment comment = new Comment.AddCommentBuilder("a comment!").Build();
Discussion discussionToCreateOnRow = new Discussion.CreateDiscussionBuilder("discussion on row", comment).Build();
Discussion discussionCreatedOnRow = smartsheet.SheetResources.RowResources.DiscussionResources.CreateDiscussionWithAttachment(sheetId, rowId, discussionToCreateOnRow, path, null);
PaginatedResult <Discussion> discussionsOnRow = smartsheet.SheetResources.RowResources.DiscussionResources
.ListDiscussions(sheetId, rowId, new DiscussionInclusion[] { DiscussionInclusion.COMMENTS }, null);
Assert.IsTrue(discussionsOnRow.Data.Count == 1);
Assert.IsTrue(discussionsOnRow.Data[0].Title == "discussion on row");
Assert.IsTrue(discussionsOnRow.Data[0].Comments[0].Text == "discussion on row\n\na comment!");
smartsheet.SheetResources.DeleteSheet(sheetId);
}
/// <summary>
/// Adds subtasks to the Smartsheet project
/// </summary>
/// <param name="smartsheetClient"></param>
/// <param name="columnMap"></param>
/// <param name="sheet"></param>
/// <param name="taskRow"></param>
/// <param name="pmTemplateTaskSSExtRow"></param>
/// <param name="subTaskRow"></param>
/// <param name="columnID"></param>
/// <param name="dependencyStartDateOffset"></param>
/// <param name="dependencySibling"></param>
/// <returns></returns>
public long AddSubTasks(SmartsheetClient smartsheetClient,
Dictionary <string, long> columnMap,
Sheet sheet, PMTask taskRow,
PMTaskSSExt pmTemplateTaskSSExtRow,
PMSubTask subTaskRow,
long?columnID,
int dependencyStartDateOffset,
long dependencySibling)
{
List <Cell> newCells = new List <Cell>();
Cell currentCell = new Cell.AddCellBuilder(columnMap[SmartsheetConstants.GanttTemplateMapping.TASK_NAME], subTaskRow.SubTaskCD).Build();
currentCell.Format = SmartsheetConstants.CellFormat.LARGE_GRAY_BACKGROUND;
newCells.Add(currentCell);
if (pmTemplateTaskSSExtRow.UsrEnableSubtaskDependency == true)
{
DateTime adjustedStartDate = taskRow.StartDate.Value.AddDays((double)dependencyStartDateOffset);
currentCell = new Cell.AddCellBuilder(columnMap[SmartsheetConstants.GanttTemplateMapping.START], adjustedStartDate).Build();
newCells.Add(currentCell);
}
else
{
currentCell = new Cell.AddCellBuilder(columnMap[SmartsheetConstants.GanttTemplateMapping.START], taskRow.StartDate).Build();
newCells.Add(currentCell);
}
currentCell = new Cell.AddCellBuilder(columnMap[SmartsheetConstants.GanttTemplateMapping.DURATION], subTaskRow.Duration.ToString()).Build();
newCells.Add(currentCell);
currentCell = new Cell.AddCellBuilder(columnMap[SmartsheetConstants.GanttTemplateMapping.COMMENTS], subTaskRow.Description).Build();
newCells.Add(currentCell);
Row currentRow = new Row.AddRowBuilder(null, true, null, null, null).SetCells(newCells).Build();
currentRow.ParentId = (long)columnID;
currentRow.Format = SmartsheetConstants.CellFormat.GRAY_BACKGROUND;
List <Row> newSSRows = new List <Row>();
newSSRows.Add(currentRow);
IList <Row> ssRows = smartsheetClient.SheetResources.RowResources.AddRows((long)sheet.Id, newSSRows);
return((long)ssRows[0].Id);
}
private static long AddRows(SmartsheetClient smartsheet, long sheetId, long columnId, Cell[] cellsToAdd)
{
Row row = new Row.AddRowBuilder(true, null, null, null, null).SetCells(cellsToAdd).Build();
IList<Row> rows = smartsheet.SheetResources.RowResources.AddRows(sheetId, new Row[] { row });
Assert.IsTrue(rows.Count == 1);
long rowId = rows[0].Id.Value;
bool foundValue = false;
foreach (Cell cell in rows[0].Cells)
{
if (cell.ColumnId == columnId)
{
Assert.IsTrue(cell.Value.ToString() == "hello");
foundValue = true;
break;
}
}
Assert.IsTrue(foundValue);
return rowId;
}
private static long AddRows(SmartsheetClient smartsheet, long sheetId, long columnId, Cell[] cellsToAdd)
{
Row row = new Row.AddRowBuilder(true, null, null, null, null).SetCells(cellsToAdd).Build();
IList <Row> rows = smartsheet.SheetResources.RowResources.AddRows(sheetId, new Row[] { row });
Assert.IsTrue(rows.Count == 1);
long rowId = rows[0].Id.Value;
bool foundValue = false;
foreach (Cell cell in rows[0].Cells)
{
if (cell.ColumnId == columnId)
{
Assert.IsTrue(cell.Value.ToString() == "hello");
foundValue = true;
break;
}
}
Assert.IsTrue(foundValue);
return(rowId);
}
public void TestDiscussionResources()
{
string accessToken = ConfigurationManager.AppSettings["accessToken"];
SmartsheetClient smartsheet = new SmartsheetBuilder().SetAccessToken(accessToken).Build(); long sheetId = CreateSheet(smartsheet);
Discussion discussionToCreate = new Discussion.CreateDiscussionBuilder("A discussion", new Comment.AddCommentBuilder("a comment").Build()).Build();
Discussion createdDiscussion = smartsheet.SheetResources.DiscussionResources.CreateDiscussion(sheetId, discussionToCreate);
long createdDiscussionId = createdDiscussion.Id.Value;
string path = "../../../IntegrationTestSDK/TestFile.txt";
Discussion createdDiscussionWithFile = smartsheet.SheetResources.DiscussionResources.CreateDiscussionWithAttachment(sheetId, discussionToCreate, path, null);
Assert.IsTrue(createdDiscussionWithFile.Comments[0].Attachments[0].Name == "TestFile.txt");
PaginatedResult<Discussion> discussions = smartsheet.SheetResources.DiscussionResources.ListDiscussions(sheetId, new DiscussionInclusion[] { DiscussionInclusion.COMMENTS, DiscussionInclusion.ATTACHMENTS }, null);
Assert.IsTrue(discussions.TotalCount == 2);
Assert.IsTrue(discussions.Data.Count == 2);
Assert.IsTrue(discussions.Data[0].Id.Value == createdDiscussion.Id.Value || discussions.Data[0].Id.Value == createdDiscussionWithFile.Id.Value);
Assert.IsTrue(discussions.Data[1].Id.Value == createdDiscussion.Id.Value || discussions.Data[1].Id.Value == createdDiscussionWithFile.Id.Value);
Discussion getDiscussionWithFile = smartsheet.SheetResources.DiscussionResources.GetDiscussion(sheetId, createdDiscussionWithFile.Id.Value);
Assert.IsTrue(getDiscussionWithFile.Title == "A discussion");
Assert.IsTrue(getDiscussionWithFile.Comments.Count == 1);
Assert.IsTrue(getDiscussionWithFile.Comments[0].Attachments.Count == 1);
Assert.IsTrue(getDiscussionWithFile.Comments[0].Attachments[0].Name == "TestFile.txt");
Row row = new Row.AddRowBuilder(true, null, null, null, null).Build();
IList<Row> rows = smartsheet.SheetResources.RowResources.AddRows(sheetId, new Row[] { row });
Assert.IsTrue(rows.Count == 1);
Assert.IsTrue(rows[0].Id.HasValue);
long rowId = rows[0].Id.Value;
Comment comment = new Comment.AddCommentBuilder("a comment!").Build();
Discussion discussionToCreateOnRow = new Discussion.CreateDiscussionBuilder("discussion on row", comment).Build();
Discussion discussionCreatedOnRow = smartsheet.SheetResources.RowResources.DiscussionResources.CreateDiscussionWithAttachment(sheetId, rowId, discussionToCreateOnRow, path, null);
PaginatedResult<Discussion> discussionsOnRow = smartsheet.SheetResources.RowResources.DiscussionResources
.ListDiscussions(sheetId, rowId, new DiscussionInclusion[] { DiscussionInclusion.COMMENTS }, null);
Assert.IsTrue(discussionsOnRow.Data.Count == 1);
Assert.IsTrue(discussionsOnRow.Data[0].Title == "discussion on row");
Assert.IsTrue(discussionsOnRow.Data[0].Comments[0].Text == "discussion on row\n\na comment!");
}
private static long CreateSheet(SmartsheetClient smartsheet)
{
Column[] columnsToCreate = new Column[] {
new Column.CreateSheetColumnBuilder("col 1", true, ColumnType.TEXT_NUMBER).Build(),
new Column.CreateSheetColumnBuilder("col 2", false, ColumnType.DATE).Build(),
new Column.CreateSheetColumnBuilder("col 3", false, ColumnType.TEXT_NUMBER).Build(),
};
Sheet createdSheet = smartsheet.SheetResources.CreateSheet(new Sheet.CreateSheetBuilder("new sheet", columnsToCreate).Build());
Assert.IsTrue(createdSheet.Columns.Count == 3);
Assert.IsTrue(createdSheet.Columns[1].Title == "col 2");
Cell cellA = new Cell.AddCellBuilder((long)createdSheet.Columns[0].Id, true).SetValue("A").SetStrict(false).Build();
Cell cellB = new Cell.AddCellBuilder((long)createdSheet.Columns[0].Id, true).SetValue("B").SetStrict(false).Build();
Cell cellC = new Cell.AddCellBuilder((long)createdSheet.Columns[0].Id, true).SetValue("C").SetStrict(false).Build();
Row rowA = new Row.AddRowBuilder(true, null, null, null, null).SetCells(new Cell[] { cellA }).Build();
Row rowB = new Row.AddRowBuilder(true, null, null, null, null).SetCells(new Cell[] { cellB }).Build();
Row rowC = new Row.AddRowBuilder(true, null, null, null, null).SetCells(new Cell[] { cellC }).Build();
smartsheet.SheetResources.RowResources.AddRows((long)createdSheet.Id, new Row[] { rowA, rowB, rowC });
return(createdSheet.Id.Value);
}
static void Main(string[] args)
{
try
{
var datasource = @"ILD-CHN-LAP-024\SQLEXPRESS";
var database = "EmployeeDB";
var username = "******";
var password = "******";
string cs = @"Data Source=" + datasource + ";Initial Catalog=" + database + ";Persist Security Info=True;User ID=" + username + ";Password="******"select * from Employee";
SqlCommand cmd = new SqlCommand(stm, conn);
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd.CommandText, conn);
dataAdapter.Fill(dt);
}
catch (MySqlException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
}
string smartsheetAPIToken = ConfigurationManager.AppSettings["AccessToken"];
Token token = new Token();
token.AccessToken = smartsheetAPIToken;
SmartsheetClient smartsheet = new SmartsheetBuilder().SetAccessToken(token.AccessToken).Build();
//Sheet sheet = smartsheet.SheetResources.ImportXlsSheet("../../../EmployeeSheet _updated.xlsx", null, 0, null);
//sheet = smartsheet.SheetResources.GetSheet(sheet.Id.Value, null, null, null, null, null, null, null);
long SheetId = Convert.ToInt64(ConfigurationManager.AppSettings["SheetId"]);
Sheet sheet = smartsheet.SheetResources.GetSheet(SheetId, null, null, null, null, null, null, null);
Console.WriteLine("Loaded " + sheet.Rows.Count + " rows from sheet: " + sheet.Name);
foreach (Column column in sheet.Columns)
{
columnMap.Add(column.Title, (long)column.Id);
}
// if(dt.Rows.Count!=sheet.Rows.Count)
if (dt.Rows.Count != sheet.Rows.Count)
{
Cell[] cellsA = null;
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
for (int j = 0; j < sheet.Columns.Count; j++)
{
cellsA = new Cell[]
{
//new Cell.AddCellBuilder(sheet.Columns[j].Id,dt.Rows[i].ItemArray.GetValue(j)).Build(),
//new Cell.AddCellBuilder(columnMap["EmployeeId"],dt.Rows[i].ItemArray.GetValue(j)).Build(),
new Cell.AddCellBuilder(columnMap["EmployeeId"], dt.Rows[i][0]).Build(),
new Cell.AddCellBuilder(columnMap["FirstName"], dt.Rows[i][1]).Build(),
new Cell.AddCellBuilder(columnMap["LastName"], dt.Rows[i][2]).Build(),
new Cell.AddCellBuilder(columnMap["Email"], dt.Rows[i][3]).Build(),
new Cell.AddCellBuilder(columnMap["Address"], dt.Rows[i][4]).Build(),
};
//Row rowA = new Row.AddRowBuilder(true, null, null, null, null).SetCells(cellsA).Build();
//smartsheet.SheetResources.RowResources.AddRows(sheet.Id.Value, new Row[] { rowA });
}
Row rowA = new Row.AddRowBuilder(true, null, null, null, null).SetCells(cellsA).Build();
smartsheet.SheetResources.RowResources.AddRows(sheet.Id.Value, new Row[] { rowA });
}
}
List <Row> rowsToUpdate = new List <Row>();
foreach (Row row in sheet.Rows)
{
Row rowToUpdate = evaluateRowAndBuildUpdates(row);
if (rowToUpdate != null)
{
rowsToUpdate.Add(rowToUpdate);
}
}
Console.WriteLine("Writing " + rowsToUpdate.Count + " rows back to sheet id " + sheet.Id);
smartsheet.SheetResources.RowResources.UpdateRows(sheet.Id.Value, rowsToUpdate);
//smartsheet.SheetResources.RowResources.DeleteRows(sheet.Id.Value, new long[] { 3413974005114756, 5366296352450436 }, true);
smartsheet.SheetResources.RowResources.DeleteRows(sheet.Id.Value, new long[] { 4207621315291012, 8144091986061188 }, true);
Console.WriteLine("Done...");
Console.ReadLine();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Adds subtasks to the Smartsheet project
/// </summary>
/// <param name="smartsheetClient"></param>
/// <param name="columnMap"></param>
/// <param name="sheet"></param>
/// <param name="taskRow"></param>
/// <param name="pmTemplateTaskSSExtRow"></param>
/// <param name="subTaskRow"></param>
/// <param name="columnID"></param>
/// <param name="dependencyStartDateOffset"></param>
/// <param name="dependencySibling"></param>
/// <returns></returns>
public long AddSubTasks(SmartsheetClient smartsheetClient,
ProjectEntry projectEntryGraph,
Dictionary <string, long> columnMap,
Sheet sheet, PMTask taskRow,
PMTaskSSExt pmTemplateTaskSSExtRow,
PMSubTask subTaskRow,
long?columnID,
int dependencyStartDateOffset,
long dependencySibling,
PXResultset <PMSSMapping> templateMappingSSRow)
{
List <Cell> newCells = new List <Cell>();
Cell currentCell;
ProjectEntry copyProjectEntryGraph = projectEntryGraph;
ProjectEntryExt graphExtended = copyProjectEntryGraph.GetExtension <ProjectEntryExt>();
if (taskRow != null)
{
taskRow.TaskCD = subTaskRow.SubTaskCD;
taskRow.Description = subTaskRow.Description;
if (pmTemplateTaskSSExtRow != null)
{
if (pmTemplateTaskSSExtRow.UsrEnableSubtaskDependency == true)
{
taskRow.StartDate = taskRow.EndDate;
}
}
foreach (PMSSMapping row in templateMappingSSRow)
{
if (!String.IsNullOrEmpty(row.NameAcu))
{
if (copyProjectEntryGraph.GetValue(SmartsheetConstants.ViewName.TASK, taskRow, row.NameAcu) is DateTime)
{
currentCell = new Cell.AddCellBuilder(columnMap[row.NameSS], (DateTime)copyProjectEntryGraph.GetValue(SmartsheetConstants.ViewName.TASK, taskRow, row.NameAcu)).Build();
currentCell.Format = SmartsheetConstants.CellFormat.LARGE_GRAY_BACKGROUND;
}
else
{
if (row.NameAcu == SmartsheetConstants.ColumnMapping.PCT_COMPLETE)
{
decimal completePercent = Convert.ToDecimal(copyProjectEntryGraph.GetValue(SmartsheetConstants.ViewName.TASK, taskRow, row.NameAcu)) / 100;
currentCell = new Cell.AddCellBuilder(columnMap[row.NameSS], completePercent).Build();
currentCell.Format = SmartsheetConstants.CellFormat.LARGER_GRAY_BACKGROUND_PERCENTAGE;
}
else
{
currentCell = new Cell.AddCellBuilder(columnMap[row.NameSS], copyProjectEntryGraph.GetValue(SmartsheetConstants.ViewName.TASK, taskRow, row.NameAcu).ToString()).Build();
currentCell.Format = SmartsheetConstants.CellFormat.LARGE_GRAY_BACKGROUND;
}
}
newCells.Add(currentCell);
}
}
}
Row currentRow = new Row.AddRowBuilder(null, true, null, null, null).SetCells(newCells).Build();
currentRow.ParentId = (long)columnID;
currentRow.Format = SmartsheetConstants.CellFormat.GRAY_BACKGROUND;
List <Row> newSSRows = new List <Row>();
newSSRows.Add(currentRow);
IList <Row> ssRows = smartsheetClient.SheetResources.RowResources.AddRows((long)sheet.Id, newSSRows);
return((long)ssRows[0].Id);
}
/// <summary>
/// Inserts Acumatica Tasks in Smartsheet
/// </summary>
/// <param name="projectEntryGraph"></param>
/// <param name="originalColumnMap"></param>
/// <param name="modifiedColumnMap"></param>
/// <param name="firstSync"></param>
/// <returns></returns>
public List <Row> InsertAcumaticaTasksInSS(ProjectEntry projectEntryGraph,
Dictionary <string, long> originalColumnMap,
Dictionary <string, long> modifiedColumnMap,
bool firstSync, PXResultset <PMSSMapping> templateMappingSet)
{
List <Row> newSSRows = new List <Row>();
Row blankRow = new Row();
if (firstSync)
{
blankRow = new Row.AddRowBuilder(null, true, null, null, null).Build();
newSSRows.Add(blankRow);
}
foreach (PMTask taskRow in projectEntryGraph.Tasks.Select())
{
PMTaskSSExt pmTaskSSExtRow = PXCache <PMTask> .GetExtension <PMTaskSSExt>(taskRow);
if (pmTaskSSExtRow != null &&
pmTaskSSExtRow.UsrSmartsheetTaskID != null)
{
continue;
}
List <Cell> newCells = new List <Cell>();
Cell currentCell = new Cell();
foreach (PMSSMapping row in templateMappingSet)
{
if (!String.IsNullOrEmpty(row.NameAcu))
{
if (!String.IsNullOrEmpty(row.NameSS))
{
if (row.NameAcu == SmartsheetConstants.ColumnMapping.DURATION)
{
currentCell = new Cell.AddCellBuilder(originalColumnMap[row.NameSS], projectEntryGraph.GetValue(SmartsheetConstants.ViewName.TASK, taskRow, row.NameAcu).ToString()).Build();
currentCell.Format = SmartsheetConstants.CellFormat.LARGE_GRAY_BACKGROUND;
}
else
{
if (row.NameAcu == SmartsheetConstants.ColumnMapping.PCT_COMPLETE)
{
decimal completePercent = Convert.ToDecimal(projectEntryGraph.GetValue(SmartsheetConstants.ViewName.TASK, taskRow, row.NameAcu)) / 100;
currentCell = new Cell.AddCellBuilder(originalColumnMap[row.NameSS], completePercent).Build();
currentCell.Format = SmartsheetConstants.CellFormat.LARGER_GRAY_BACKGROUND_PERCENTAGE;
}
else
{
if (row.NameAcu == SmartsheetConstants.ColumnMapping.TASKS_CD)
{
taskRow.TaskCD = taskRow.TaskCD.Trim();
}
currentCell = new Cell.AddCellBuilder(originalColumnMap[row.NameSS], projectEntryGraph.GetValue(SmartsheetConstants.ViewName.TASK, taskRow, row.NameAcu)).Build();
currentCell.Format = SmartsheetConstants.CellFormat.LARGE_GRAY_BACKGROUND;
}
}
newCells.Add(currentCell);
}
}
}
Row currentRow = new Row.AddRowBuilder(null, true, null, null, null).SetCells(newCells).Build();
currentRow.Format = SmartsheetConstants.CellFormat.GRAY_BACKGROUND;
newSSRows.Add(currentRow);
blankRow = new Row.AddRowBuilder(null, true, null, null, null).Build();
newSSRows.Add(blankRow);
}
return(newSSRows);
}
/// <summary>
/// Inserts Acumatica Tasks in Smartsheet
/// </summary>
/// <param name="projectEntryGraph"></param>
/// <param name="originalColumnMap"></param>
/// <param name="modifiedColumnMap"></param>
/// <param name="firstSync"></param>
/// <returns></returns>
public List <Row> InsertAcumaticaTasksInSS(ProjectEntry projectEntryGraph,
Dictionary <string, long> originalColumnMap,
Dictionary <string, long> modifiedColumnMap,
bool firstSync)
{
List <Row> newSSRows = new List <Row>();
Row blankRow = new Row();
if (firstSync)
{
blankRow = new Row.AddRowBuilder(null, true, null, null, null).Build();
newSSRows.Add(blankRow);
}
foreach (PMTask taskRow in projectEntryGraph.Tasks.Select())
{
PMTaskSSExt pmTaskSSExtRow = PXCache <PMTask> .GetExtension <PMTaskSSExt>(taskRow);
if (pmTaskSSExtRow != null &&
pmTaskSSExtRow.UsrSmartsheetTaskID != null)
{
continue;
}
List <Cell> newCells = new List <Cell>();
Cell currentCell = new Cell();
//Task
if (firstSync)
{
currentCell = new Cell.AddCellBuilder(originalColumnMap[SmartsheetConstants.GanttTemplateMapping.TASK_NAME], taskRow.TaskCD).Build();
}
else
{
currentCell = new Cell.AddCellBuilder(modifiedColumnMap[SmartsheetConstants.ColumnMapping.TASK_ID], taskRow.TaskCD).Build();
}
currentCell.Format = SmartsheetConstants.CellFormat.LARGE_BOLD_GRAY_BACKGROUND;
newCells.Add(currentCell);
//Dates
if (taskRow.StartDate != null && taskRow.EndDate != null)
{
if (firstSync)
{
currentCell = new Cell.AddCellBuilder(originalColumnMap[SmartsheetConstants.GanttTemplateMapping.START], taskRow.StartDate).Build();
}
else
{
currentCell = new Cell.AddCellBuilder(modifiedColumnMap[SmartsheetConstants.ColumnMapping.START_DATE], taskRow.StartDate).Build();
}
currentCell.Format = SmartsheetConstants.CellFormat.LARGER_GRAY_BACKGROUND;
newCells.Add(currentCell);
TimeSpan dateDifference = (TimeSpan)(taskRow.EndDate - taskRow.StartDate);
if (firstSync)
{
currentCell = new Cell.AddCellBuilder(originalColumnMap[SmartsheetConstants.GanttTemplateMapping.DURATION], (dateDifference.Days + 1).ToString()).Build();
}
else
{
currentCell = new Cell.AddCellBuilder(modifiedColumnMap[SmartsheetConstants.ColumnMapping.DURATION], (dateDifference.Days + 1).ToString()).Build();
}
currentCell.Format = SmartsheetConstants.CellFormat.LARGER_GRAY_BACKGROUND;
newCells.Add(currentCell);
}
//Completed %
if (taskRow.CompletedPercent != null)
{
if (firstSync)
{
currentCell = new Cell.AddCellBuilder(originalColumnMap[SmartsheetConstants.GanttTemplateMapping.PCT_COMPLETE], (double)taskRow.CompletedPercent / 100).Build();
}
else
{
currentCell = new Cell.AddCellBuilder(modifiedColumnMap[SmartsheetConstants.ColumnMapping.PCT_COMPLETE], (double)taskRow.CompletedPercent / 100).Build();
}
currentCell.Format = SmartsheetConstants.CellFormat.LARGER_GRAY_BACKGROUND_PERCENTAGE;
newCells.Add(currentCell);
}
if (taskRow.Description != null)
{
if (firstSync)
{
currentCell = new Cell.AddCellBuilder(originalColumnMap[SmartsheetConstants.GanttTemplateMapping.COMMENTS], taskRow.Description).Build();
}
else
{
currentCell = new Cell.AddCellBuilder(modifiedColumnMap[SmartsheetConstants.ColumnMapping.DESCRIPTION], taskRow.Description).Build();
}
currentCell.Format = SmartsheetConstants.CellFormat.LARGE_GRAY_BACKGROUND;
newCells.Add(currentCell);
}
Row currentRow = new Row.AddRowBuilder(null, true, null, null, null).SetCells(newCells).Build();
currentRow.Format = SmartsheetConstants.CellFormat.GRAY_BACKGROUND;
newSSRows.Add(currentRow);
blankRow = new Row.AddRowBuilder(null, true, null, null, null).Build();
newSSRows.Add(blankRow);
}
return(newSSRows);
}
public static void SampleCode()
{
// Set the Access Token
Token token = new Token();
token.AccessToken = "YOUR_TOKEN";
// Use the Smartsheet Builder to create a Smartsheet
SmartsheetClient smartsheet = new SmartsheetBuilder().SetAccessToken(token.AccessToken).Build();
#pragma warning disable 0162
if (false)
{
// List all contents (specify 'include' parameter with value of "source").
Home home = smartsheet.HomeResources.GetHome(new HomeInclusion[] { HomeInclusion.SOURCE });
// List folders in "Sheets" folder (specify 'includeAll' parameter with value of "true").
IList<Folder> homeFolders = home.Folders;
foreach (Folder tmpFolder in homeFolders)
{
Console.WriteLine("folder:" + tmpFolder.Name);
}
// List sheets (omit 'include' parameter and pagination parameters).
PaginatedResult<Sheet> homeSheetsResult = smartsheet.SheetResources.ListSheets(null, null);
foreach (Sheet tmpSheet in homeSheetsResult.Data)
{
Console.WriteLine("sheet:" + tmpSheet.Name);
}
// Create folder in home
Folder folder = new Folder.CreateFolderBuilder("New Folder").Build();
folder = smartsheet.HomeResources.FolderResources.CreateFolder(folder);
Console.WriteLine("Folder ID:" + folder.Id + ", Folder Name:" + folder.Name);
//=========================================
}
// Setup text Column Object
Column textColumn = new Column.CreateSheetColumnBuilder("To Do List", true, ColumnType.TEXT_NUMBER).Build();
// Setup checkbox Column Object
Column checkboxColumn = new Column.CreateSheetColumnBuilder("Finished", false, ColumnType.CHECKBOX).Build();
// Add the 2 Columns (flag & text) to a new Sheet Object
Sheet sheet = new Sheet.CreateSheetBuilder("New Sheet", new Column[] { textColumn, checkboxColumn }).Build();
// Send the request to create the sheet @ Smartsheet
sheet = smartsheet.SheetResources.CreateSheet(sheet);
//=========================================
// Example: Add two rows
Func<string, Row> addRowHelper = primaryColVal =>
{
var cells = new Cell[] { new Cell.AddCellBuilder(sheet.GetColumnByIndex(0).Id, primaryColVal).Build() };
var rows = new Row[] { new Row.AddRowBuilder(true, null, null, null, null).SetCells(cells).Build() };
return smartsheet.SheetResources.RowResources.AddRows(sheet.Id.Value, rows)[0];
};
var rowA = addRowHelper("a");
var rowB = addRowHelper("b");
// Example: Move rowB above rowA
var updateRowRequest = new Row.UpdateRowBuilder(rowB.Id)
.SetSiblingId(rowA.Id)
.SetAbove(true)
.Build();
var updateRowResponse = smartsheet.SheetResources.RowResources.UpdateRows(sheet.Id.Value, new Row[] { updateRowRequest });
//Indent rowA under rowB
updateRowRequest = new Row.UpdateRowBuilder(rowA.Id)
.SetParentId(rowB.Id)
.Build();
updateRowResponse = smartsheet.SheetResources.RowResources.UpdateRows(sheet.Id.Value, new Row[] { updateRowRequest });
//=========================================
// Example: Add two rows
PaginatedResult<Sheet> sheetsSince = smartsheet.SheetResources.ListSheets(null, null, DateTime.UtcNow.AddDays(-5));
PaginatedResult<Sheet> orgSheetsSince = smartsheet.UserResources.SheetResources.ListOrgSheets(null, DateTime.UtcNow.AddDays(-10));
PaginatedResult<Report> reportsSince = smartsheet.ReportResources.ListReports(null, DateTime.UtcNow.AddDays(-5));
PaginatedResult<Sight> sightsSince = smartsheet.SightResources.ListSights(null, DateTime.Now.AddDays(-5));
//=========================================
// Example: setup a webhook
Webhook webhook = new Webhook();
webhook.Name = "Test webhook";
webhook.CallbackUrl = "https://someurl.com";
webhook.Scope = "sheet";
webhook.ScopeObjectId = 6810644029695876L;
IList<string> events = new List<string>();
events.Add("*.*");
webhook.Events = events;
webhook.Version = 1;
webhook = smartsheet.WebhookResources.CreateWebhook(webhook);
PaginatedResult<Webhook> webhooks = smartsheet.WebhookResources.ListWebhooks(null);
//// Update the shared secret
webhook = smartsheet.WebhookResources.GetWebhook(webhooks.Data[0].Id.Value);
Webhook update_webhook = new Webhook();
update_webhook.Enabled = true;
update_webhook.Id = webhooks.Data[0].Id;
update_webhook = smartsheet.WebhookResources.UpdateWebhook(update_webhook);
WebhookSharedSecret shared = smartsheet.WebhookResources.ResetSharedSecret(update_webhook.Id.Value);
smartsheet.WebhookResources.DeleteWebhook(update_webhook.Id.Value);
//=========================================
// Example: sights example
PaginatedResult<Sight> sights = smartsheet.SightResources.ListSights(null);
if (sights.TotalCount > 0)
{
Sight sight = smartsheet.SightResources.GetSight(sights.Data[0].Id.Value);
ContainerDestination dest = new ContainerDestination();
dest.DestinationType = DestinationType.FOLDER;
dest.DestinationId = 3167806247200644L;
dest.NewName = "My New Sight";
sight = smartsheet.SightResources.CopySight(sights.Data[0].Id.Value, dest);
Sight newsight = new Sight();
newsight.Id = sight.Id;
newsight.Name = "My New New Sight";
smartsheet.SightResources.UpdateSight(newsight);
ContainerDestination destToo = new ContainerDestination();
destToo.DestinationType = DestinationType.FOLDER;
destToo.DestinationId = 3110683182163844L;
sight = smartsheet.SightResources.MoveSight(sight.Id.Value, destToo);
smartsheet.SightResources.DeleteSight(sight.Id.Value);
PaginatedResult<Share> sightShares = smartsheet.SightResources.ShareResources.ListShares(sights.Data[0].Id.Value, null, ShareScope.Workspace);
}
//=========================================
// Example: cell images and partial success
Sheet imageSheet = smartsheet.SheetResources.GetSheet(6810644029695876L, null, null, null, null, null, null, null);
Cell[] cellsA = new Cell[] { new Cell.AddCellBuilder(imageSheet.Columns[0].Id.Value, true).Build(), new Cell.AddCellBuilder(imageSheet.Columns[1].Id.Value, "Foobar").Build() };
rowA = new Row.AddRowBuilder(true, null, null, null, null).SetCells(cellsA).Build();
Cell[] cellsB = new Cell[] { new Cell.AddCellBuilder(imageSheet.Columns[1].Id.Value, true).Build(), new Cell.AddCellBuilder(imageSheet.Columns[1].Id.Value, "Barfoo").Build() };
rowB = new Row.AddRowBuilder(true, null, null, null, null).SetCells(cellsB).Build();
BulkItemRowResult bulkAddResult = smartsheet.SheetResources.RowResources.AddRowsAllowPartialSuccess(6810644029695876L, new Row[] { rowA, rowB });
Cell[] cellsC = new Cell[] { new Cell.AddCellBuilder(imageSheet.Columns[0].Id.Value, true).Build(), new Cell.AddCellBuilder(imageSheet.Columns[1].Id.Value, "FoobarII").Build() };
Row rowC = new Row.UpdateRowBuilder(imageSheet.Rows[2].Id.Value).SetCells(cellsC).Build();
Cell[] cellsD = new Cell[] { new Cell.AddCellBuilder(imageSheet.Columns[1].Id.Value, true).Build(), new Cell.AddCellBuilder(imageSheet.Columns[1].Id.Value, "BarfooII").Build() };
Row rowD = new Row.UpdateRowBuilder(imageSheet.Rows[1].Id.Value).SetCells(cellsD).Build();
BulkItemRowResult bulkUpdateResult = smartsheet.SheetResources.RowResources.UpdateRowsAllowPartialSuccess(6810644029695876L, new Row[] { rowC, rowD });
ImageUrl imageUrl = new ImageUrl.ImageUrlBuilder("aVbWbXMsD5KsOu2 - CWtwSA").Build();
IList<ImageUrl> imageUrls = new List<ImageUrl>();
imageUrls.Add(imageUrl);
ImageUrlMap imageUrlMap = smartsheet.ImageUrlResources.GetImageUrls(imageUrls);
smartsheet.SheetResources.RowResources.CellResources.AddImageToCell(6810644029695876L, imageSheet.Rows[0].Id.Value, imageSheet.Columns[1].Id.Value,
"d:\\Smartsheet\\vHepGiJaeL6GPOX3wAx8yaxD75ym5eAbk0GB-MSz0gc.png", "image/png");
//=========================================
//// Update two cells on a row
//IList<Cell> cells = new Cell.UpdateRowCellsBuilder().AddCell(5111621270955908L, "test11", false).
// AddCell(2859821457270660L, "test22").Build();
//smartsheet.Rows().UpdateCells(6497447011739524L, cells);
////=========================================
//// Create a row and sheet level discussion with an initial comment
//Comment comment = new Comment.AddCommentBuilder().SetText("Hello World").Build();
//Discussion discussion = new Discussion.CreateDiscussionBuilder().SetTitle("New Discussion").
// SetComment(comment).Build();
//smartsheet.Rows().Discussions().CreateDiscussion(6497447011739524L, discussion);
//smartsheet.Sheets().Discussions().CreateDiscussion(7370846613333892L, discussion);
////=========================================
//// Update a folder name
//folder = new Folder.UpdateFolderBuilder().SetName("A Brand New New Folder").SetID(2545279862892420L).Build();
//smartsheet.Folders().UpdateFolder(folder);
////=========================================
//// Create 3 users to share a sheet with
//IList<User> users = new List<User>();
//User user = new User();
//user.Email = "*****@*****.**";
//users.Add(user);
//User user1 = new User();
//user1.Email = "*****@*****.**";
//users.Add(user1);
//User user2 = new User();
//user2.Email = "*****@*****.**";
//users.Add(user2);
//// Add the message, subject & users to share with
//MultiShare multiShare = new MultiShare.ShareToManyBuilder().SetMessage("Here is the sheet I am sharing with you").
// SetAccessLevel(AccessLevel.VIEWER).SetSubject("Sharing a Smartsheet with you").SetUsers(users).Build();
//// Share the specified sheet with the users.
//smartsheet.Sheets().Shares().ShareTo(7370846613333892L, multiShare, true);
////=========================================
//// Create a single share to a specified email address with the specified access level
//Share share = new Share.ShareToOneBuilder().SetEmail("*****@*****.**").SetAccessLevel(AccessLevel.VIEWER)
// .Build();
//// Add the share to a specific sheet
//smartsheet.Sheets().Shares().ShareTo(7370846613333892L, share);
////=========================================
//// Create a share with the specified access level
//share = new Share.UpdateShareBuilder().SetAccessLevel(AccessLevel.VIEWER).Build();
//// Update the share permission on the specified sheet for the specified user.
//smartsheet.Sheets().Shares().UpdateShare(7370846613333892L, 3433212006426500L, share);
////=========================================
//// Create 3 cells
//Cell cell = new Cell();
//cell.Value = "Cell1";
//cell.Strict = false;
//cell.ColumnId = 5111621270955908L;
//Cell cell2 = new Cell();
//cell2.Value = "Cell2";
//cell2.ColumnId = 2859821457270660L;
//Cell cell3 = new Cell();
//cell3.Value = "cell3";
//cell3.ColumnId = 7877251644581764L;
//// Store the cells in a list
//List<Cell> cells1 = new List<Cell>();
//cells1.Add(cell);
//cells1.Add(cell2);
//cells1.Add(cell3);
//// Create a row and add the list of cells to the row
//Row row = new Row();
//row.Cells = cells1;
//// Add two rows to a list of rows.
//List<Row> rows = new List<Row>();
//rows.Add(row);
//rows.Add(row);
//// Add the rows to the row wrapper and set the location to insert the rows
//RowWrapper rowWrapper = new RowWrapper.InsertRowsBuilder().SetRows(rows).SetToBottom(true).Build();
//// Add the rows to the specified sheet
//smartsheet.Sheets().Rows().InsertRows(7370846613333892L, rowWrapper);
//// Setup a row to be moved to the top of a sheet
//RowWrapper rowWrapper1 = new RowWrapper.MoveRowBuilder().SetToTop(true).Build();
//// Move the specified row
//smartsheet.Rows().MoveRow(5701744190613380L, rowWrapper1);
////=========================================
//// Create a sheet that is a copy of a template
//Sheet sheet1 = new Sheet.CreateFromTemplateOrSheetBuilder().SetFromId(7370846613333892L).
// SetName("Copy of a Template").Build();
//// Create the new sheet from the template
//smartsheet.Sheets().CreateSheetFromExisting(sheet1, new ObjectInclusion[] { ObjectInclusion.DATA,
// ObjectInclusion.ATTACHMENTS, ObjectInclusion.DISCUSSIONS });
////=========================================
//// Setup a sheet with a new name
//Sheet sheet2 = new Sheet.UpdateSheetBuilder().SetName("TESTING123").SetID(7370846613333892L).Build();
//// Update the sheet with the new name
//smartsheet.Sheets().UpdateSheet(sheet2);
////=========================================
//// Setup a publishing status to give a rich version of the sheet as read only
//SheetPublish publish = new SheetPublish.PublishStatusBuilder().SetReadOnlyFullEnabled(true).
// SetReadOnlyLiteEnabled(false).SetIcalEnabled(false).SetReadWriteEnabled(false).Build();
//// Setup the specified sheet with the new publishing status
//smartsheet.Sheets().UpdatePublishStatus(7370846613333892L, publish);
////=========================================
//// Setup a user with an email address and full permission
//User user3 = new User.AddUserBuilder().SetEmail("*****@*****.**").SetAdmin(true).
// SetLicensedSheetCreator(true).Build();
//// Create the user account
//smartsheet.Users().AddUser(user3);
////=========================================
//// Setup a user with new privileges
//User user4 = new User.UpdateUserBuilder().SetAdmin(false).SetLicensedSheetCreator(false).
// SetID(4187958019417988L).Build();
//// Send the request to update the users account with the new privileges
//smartsheet.Users().UpdateUser(user4);
////=========================================
//// Create a workspace with a specific name and ID
//Workspace workspace = new Workspace.UpdateWorkspaceBuilder().SetName("Workspace Name1").
// SetID(8257948486002564L).Build();
//// Update the workspace with the new name.
//smartsheet.Workspaces().UpdateWorkspace(workspace);
////=========================================
//smartsheet.Sheets().Attachments().AttachFile(4844590336370564L,
// @"../../../TestSDK/resources/getPDF.pdf", "application/pdf");
//Attachment attach = new Attachment();
//attach.Name = "Test";
//attach.Url = "http://google.com";
//attach.AttachmentType = AttachmentType.LINK;
//smartsheet.Sheets().Attachments().AttachURL(4844590336370564L, attach);
}