I want to simply connect to a sheet, pull data from a cell into my scripting interface, and update that cell from my code as well. There are a small hand full of approaches for different needs - this one works easily.
Let's create a function that pulls a numeric value from a cell and increments that value by one. I'm doing this for astrophotography processing, so that value will represent the number of "Frames" in a stack of photos.
1. Create a Function
From Google Sheets, choose Extensions > Apps Script to open the Code.gs coding interface for your spreadsheet. Use the default or create a new custom function name.
function addFrame() {
}
2. Connect to your Spreadsheet
Add 3 variables to connect to your:- Current spreadsheet file/item in drive. That's the whole document with the title at the top of the page
- Current sheet (in case you have multiples sheets added to a spreadsheet. In this case we'll just point to the first [0] sheet in the spreadsheet
- Specific cell that you want to interact with with (row,column) notation
function addFrame() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var cell = sheet.getRange(2,5); }
3. Pull a Value from a Cell
Store the value using cell.getValue() in a new variable
function addFrame() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var cell = sheet.getRange(2,5); var currentValue = cell.getValue(); console.log(currentValue); }
4. Write a New Value to a Cell
cell.setValue() will do the job
function addFrame() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var cell = sheet.getRange(2,5); var currentValue = cell.getValue(); cell.setValue(currentValue + 1); }
5. Assign Script to Control Button
If you haven't already, you can Insert > Insert Drawing to design a button, add it to your sheet, and assign a script to run every time you click that object like a GUI control.
Extra Credit: Refactor & Pass Values from Other Functions
Let's add comments, add some basic logic, and move the Add and Remove commands outside of the Processing function. Instead we'll pass the Add or Remove value into the Processing function as a positive or negative value.
// Add or Remove Frames from the Calculator // Perform the addition or removal of frames from cell D2 function processFrames(frameChange) { // Setup var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var cell = sheet.getRange(2,5); // Get current value of Frames var currentValue = cell.getValue(); // Ensure proper number of frames if (currentValue < 30 && currentValue > 1) { cell.setValue(currentValue + frameChange); // Update the frame value } else { // Cannot add more than 30 frames. Do nothing console.log("Maximum or mimumum number of frames are already set."); } } function addFrame() { processFrames(1); // Add 1 } function removeFrame() { processFrames(-1); // Remove 1 }
Here's a link to view the spreadsheet, but you'll need to allow a bunch of security permissions to use shared code over Google.
