Google Sheets Apps Script Basic Setup

 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.