SpreadJS, a JavaScript spreadsheet, can be used in many different applications and industries, with different features and use cases for almost any scenario. In this blog, we will show you how you can leverage the power and features of SpreadJS in an application for a company in the media industry. Specifically, this blog will show you how to create a content calendar to show when content is scheduled to be released and a tracker for article releases.
Download the sample to follow along with this blog.
Ready to see what else SpreadJS can be used for? Download a Free 30-day trial today!
Setting Up SpreadJS
We can start by creating an HTML page and a JavaScript file to go along with it:
<!doctype html>
<html lang="en" style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="./node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<link rel="stylesheet" type="text/css" href="styles.css">
<script src="./node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
</head>
<body>
<div class="sample-tutorial">
<div id="ss" style="width:100%;height:100%"></div>
</div>
</body>
</html>In this case, we can install SpreadJS in the application using NPM in a terminal:
npm install @grapecity/spread-sheetsDesign the Calendar Template
The SpreadJS Designer makes creating templates really simple. We can start by creating the calendar in a sheet. Add a drop-down in a cell with a date that we can use to populate the calendar. The drop-down can be set as a custom name called “currentMonth”.
We can then set up the days in a row, starting with Sunday and ending on Saturday. Once we have these two things, we can create a dynamic array function to populate the month with the correct dates. In this case, we will utilize the SEQUENCE and WEEKDAY functions. We can also format the days as Dates:
=SEQUENCE(6,7,currentMonth-WEEKDAY(currentMonth)+1,1)
In this example, we will also use a range template for each day in the calendar to display specific data so we can do that now. Simply create a new sheet and format the cells in it as follows. We can then set up sheet binding and drag the fields into the cells from the side panel:
The last thing we can do in the SpreadJS Designer is to create a table with some data that we can use for the calendar. We can create another sheet with a table in it. To do this, follow these steps:
- Add a sheet with the “+” icon in the sheet tab at the bottom
- Highlight the cells where you want the table to go
- Click on INSERT>Table and click “OK” in the popup menu
- In the TABLE DESIGN Tab that opens up, change the Table Name by typing in the textbox at the top left
Now that we have the design of the calendar done, we can save it for loading in our application. To save it for editing later, we can go to FILE>Save>Save SJS File. In this case, I’ve called it “media_template.sjs”:
To load the file in our application, we need to export it as a JS file. We can do this by going to FILE>Export>SpreadJS JSON>Export JavaScript File:
We need to make sure we add a reference to this new file in our HTML file:
<script src="media_template.js" type="text/javascript"></script>Creating and Setting the Range Template
Now that we have everything designed and loaded, we can create a range template based on the sheet we created that will load in each field from the table we made for the specific day in the calendar. Once we have that, we can set it as the formatter for all of the cells in the calendar:
function createRangeTemplates(spread) {
var calendarSheet = spread.getSheetFromName("Content Calendar");
calendarSheet.suspendPaint();
var format = '=IFERROR(RANGEBLOCKSPARKLINE("Day Template"!$A$2:$C$4,'
+ 'OBJECT('
+ '"date",@,'
+ '"task",IFERROR(FILTER(calendarTable[Task],calendarTable[Date]=@),""),'
+ '"editor",IFERROR(FILTER(calendarTable[Editor],calendarTable[Date]=@),""),'
+ '"status",IFERROR(FILTER(calendarTable[Status],calendarTable[Date]=@),""),'
+ '"month",MONTH($B$4))),"")';
calendarSheet.getCell(6, 1).formatter(format);
for (var r = 6; r <= 11; r++) {
for (var c = 1; c <= 7; c++) {
calendarSheet.getCell(r, c).formatter(format);
}
}
calendarSheet.resumePaint();
}This will result in a correctly formatted calendar with the data that we created:
Media Coverage Tracker
We can now add the last piece of this application: the media coverage tracker. This shows all of the articles and their status’ for tracking purposes. We can start by opening our .SJS file in the SpreadJS Designer, creating a new sheet and adding two tables to it, one called “ArticlesPlanned” and the other “ArticlesLive”. This can be done with the same process we used earlier:
We can save this file and export it to JS as we did before. This should now load into our application with the new sheet. We can now start adding data to these tables, and we can do so by creating an object to bind the tables to:
function createMediaCoverageTables(spread) {
var mediaCoverageTrackerSheet = spread.getSheetFromName("Media Coverage Tracker");
var mediaCoverageData = {
articlesLive: [
{article: "Businesses and Spreadsheets", publication: "NYTimes", type: "Feature", date: "7/3/2023", status: "Live"},
{article: "Top Spreadsheet Software 2023", publication: "Forbes", type: "Review", date: "7/6/2023", status: "Live"},
{article: "New Spreadsheet Software Releases", publication: "WIRED", type: "Release", date: "7/13/2023", status: "Live"},
{article: "Upcoming Excel Changes", publication: "CNET", type: "Broadcast", date: "7/17/2023", status: "Live"}
],
articlesPlanned: [
{article: "The Best Spreadsheet Functions for Your Workbook", publication: "The Verge", type: "Review", date: "7/20/2023", status: "Planned"},
{article: "Spreadsheets in Layman's Terms", publication: "Medium", type: "Feature", date: "7/24/2023", status: "Planned"}
]
};
...
}Now that we have that data, we can get the tables from the sheet, create the table columns, and bind each table to its own data source from the object we created above:
function createMediaCoverageTables(spread) {
...
mediaCoverageTrackerSheet.suspendPaint();
var tableArticlesLive = mediaCoverageTrackerSheet.tables.findByName("ArticlesLive");
var tableArticlesPlanned = mediaCoverageTrackerSheet.tables.findByName("ArticlesPlanned");
var tc1 = new GC.Spread.Sheets.Tables.TableColumn();
tc1.name("Article");
tc1.dataField("article");
var tc2 = new GC.Spread.Sheets.Tables.TableColumn();
tc2.name("Publication");
tc2.dataField("publication");
var tc3 = new GC.Spread.Sheets.Tables.TableColumn();
tc3.name("Type");
tc3.dataField("type");
var tc4 = new GC.Spread.Sheets.Tables.TableColumn();
tc4.name("Date");
tc4.dataField("date");
tc4.formatter("M/d/yyyy");
tableArticlesLive.autoGenerateColumns(false);
tableArticlesLive.bind([tc1, tc2, tc3, tc4], 'articlesLive', mediaCoverageData);
tableArticlesPlanned.autoGenerateColumns(false);
tableArticlesPlanned.bind([tc1, tc2, tc3, tc4], 'articlesPlanned', mediaCoverageData);
mediaCoverageTrackerSheet.resumePaint();
}Once we run this code, we’ll have the media track and content calendar loaded in the workbook with all of their data:
That is all it takes to create a simple media application with SpreadJS! This can be expanded upon with more functionality and data, but this is the perfect starting point for implementing your own application. Check out our documentation for more information about the features of SpreadJS, and try out our demos to see features in action.
To get started, be sure to download a trial of SpreadJS today!
Tags:
Related Blogs
Try Our JavaScript Spreadsheet Components
SpreadJS Resources
Kevin Ashley