# Save Spreadsheet Changes
A walkthrough showing how to protect a sheet, get dirty cell information, and save changes to a database
## Content
SpreadJS lets you store modifications to a spreadsheet. In the sections below, let's discuss the step-by-step process required.
## Save Spreadsheet Changes and Refresh
The following section describes the steps to store changes made in a spreadsheet and to remove all the changes by refreshing the spreadsheet.
### Step 1: Modify Spreadsheet and Protect it
1. Modify the **refresh** function created in the [Data Binding](/spreadjs/docs/v18/getstarted/quick-start/binding-to-datasource) section. After invoking **loadTable** function, execute the **suspendDirty** and **resumeDirty** methods so that the changes made to column F are not stored.
2. Set the value, background color, and horizontal al.ignment for cell F1 of the spreadsheet.
3. Set the **isProtected** option to true to protect the spreadsheet.
4. Set the **locked** method to false to prevent the locking of cells in the spreadsheet.
```javascript
//1: Set the value, backcolor, and halign between suspending / resume dirty & Protect the sheet and lock F column
function refresh() {
var ss = GC.Spread.Sheets.findControl(document.getElementById("ss"));
// Get activesheet
var sheet = ss.getActiveSheet();
// Reset the sheet and set the column count
sheet.reset();
sheet.setColumnCount(7);
// Load the table to the Spread instance using the sample data
var data = createSampleData();
loadTable(ss, data);
// Set the value, backcolor, and halign between suspending / resume dirty
// Suspend dirty cells
sheet.suspendDirty();
// Set the value, background color and halign for cell F1
sheet
.getCell(0, 5)
.backColor("yellow")
.value("Changes")
.hAlign(GC.Spread.Sheets.HorizontalAlign.center);
// Resume dirty cells
sheet.resumeDirty();
// Protect the sheet and lock F column
sheet.options.isProtected = true;
// 4.1) Setting the sheets default stye to not lock the cells
var s = sheet.getDefaultStyle();
s.locked = false;
sheet.setDefaultStyle(s);
// 4.1) Specifying to lock column F to display the new and old value (column index 5)
sheet.getRange(-1, 5, -1, 1).locked(true);
```
5. Create an HTML input button ' Refresh' that will execute the refresh function.
```javascript
```
### Step 2: Get Dirty Cell Information
1. Create **showChanges** function and use **getDirtyCells** and **hasPendingChanges** methods to get information on dirty cells along with the pending changes.
2. Create a data array, **dirtyDataArray,** to store the dirty cell information about row, col, rowCount, and colCount.
3. Use **setValue** method to set the value of cell in column F such that it displays the old and the new values of dirty cells in it.
```javascript
// Step 2: Create showChanges()
function showChanges(ss, row, col, rowCount, colCount) {
// Get activesheet
var sheet = ss.getActiveSheet();
// If the activesheet has pending changes
if (sheet.hasPendingChanges()) {
// Suspend Paint, Event, Dirty
ss.suspendPaint();
ss.suspendEvent();
sheet.suspendDirty();
// Store dirty cell collection in data array called dirtyDataArray
var dirtyDataArray = sheet.getDirtyCells(row, col, rowCount, colCount);
// Skip dirty changes for column 'F'
for (var i = 0; i < dirtyDataArray.length; i++) {
// Store dirty cell data in the dirty data array
var dirtyCell = dirtyDataArray[i];
// Display the old and new value from the dirtyDataArray values
// row index --> dirtyCell.row returns the row index of the row that had the changes made
// column index --> 5 is for the row index for column F
sheet.setValue(
dirtyCell.row,
5,
"old: " + dirtyCell.oldValue + ", new: " + dirtyCell.newValue
);
}
// Resume dirty, event, paint
sheet.resumeDirty();
ss.resumeEvent();
ss.resumePaint();
}
}
```
### Step 3: Display Dirty Cell Values
Bind **CellChanged** and **RangeChanged** events to the active sheet to execute the **showChange** function each time the spreadsheet is modified.
```auto
// Step 3: Bind CellChanged and RangeChanged events to the sheet
$(document).ready(function () {
// initializing Spread
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
// Invoke the refresh function
refresh();
// Bind CellChanged
spread.bind(GC.Spread.Sheets.Events.CellChanged, function (event, data) {
var row = data.row,
col = data.col;
if (
data.propertyName !== "value" ||
row === undefined ||
col === undefined
) {
return;
}
// Execute the showChanged function
showChanges(spread, row, col);
});
// Bind RangeChanged
spread.bind(GC.Spread.Sheets.Events.RangeChanged, function (event, data) {
var row = data.row,
col = data.col,
rowCount = data.rowCount,
colCount = data.colCount;
if (
row === undefined ||
col === undefined ||
rowCount === undefined ||
colCount === undefined
) {
return;
}
// Execute the showChanged function
showChanges(spread, row, col, rowCount, colCount);
});
});
```
The old and new values will appear in column F. Also, column F is locked so that its content cannot be modified. You can also refresh the spread instance using the **Refresh** button, to remove all changes. The output of the above code will look like the below:
## Save Spreadsheet Changes to Database
The following section describes the steps to save spreadsheet changes to the database.
> **Note:** SpreadJS does not update the database directly, but you can get the changed data by the following method and update the database accordingly.
### Step 1: Get Dirty Cell Information and Convert it to JSON String
1. Create **saveToDB** function and use **getDirtyCells** method to get information about changed cells, known as dirty cells.
2. Use **JSON.stringify** method to convert dirty cell information to JSON string.
```javascript
//1: Create saveToDB()
function saveToDB() {
// Get the activesheet from the DOM element "ss"
var ss = GC.Spread.Sheets.findControl(document.getElementById("ss"));
var sheet = ss.getActiveSheet();
// Store the dirty cells information in the dirtyCells variable
var dirtyCells = sheet.getDirtyCells();
// Get the HTML
element "msg" by id, this will be created in Step 2
var div = document.getElementById("msg");
// Display the JSON string in the div element to visualize the changed cell information
div.innerHTML = JSON.stringify(dirtyCells, null, 4);
// Note: Post changes to your database
}
```
### Step 2: Save Changes to Database
1. Create an HTML `
` element with **msg** ID after SpreadJS `
` element to display JSON string.
2. Create an input button **Save to DB** where the **saveToDB** function is invoked. The changes captured and converted to JSON string will be displayed in the **msg** ID `
` when clicking the Save to DB button.
The new HTML `` with all buttons and `
` elements will look like below:
```auto
Save changes made to the spreadsheet to a database
During this section we will create a Save to DB button and a DIV to
display the changes as a JSON string when the Save to DB button is
clicked.
Changes saved to database:
```
The JSON string which contains the changes and will be posted to the database is displayed in the output as shown below:
