Add numbers in Google Sheets with a custom script

Other Questions Google Sheets
1

I’m trying to create a script in Google Sheets that asks for a number and adds it to the value in the selected cell. Here’s what I’ve got so far:

function addPoints() {
  let ui = SpreadsheetApp.getUi();
  let input = ui.prompt('Enter points to add').getResponseText();
  
  let sheet = SpreadsheetApp.getActiveSheet();
  let cell = sheet.getActiveCell();
  let currentValue = cell.getValue();
  
  cell.setValue(input + currentValue);
}

The script works, but it’s not adding the numbers correctly. For example, if the cell has 45 and I enter 2, it gives me 452 instead of 47.

Does anyone know what I’m doing wrong? I’m new to Google Sheets scripting and could use some help. Thanks!

2

hey mate, ive had this problem before. the trick is to use parseInt() or parseFloat() to turn the strings into numbers. heres a quick fix:

cell.setValue(parseFloat(input) + parseFloat(currentValue));

that should do the trick! let me know if u need any more help

3

The issue you’re encountering is due to JavaScript’s type coercion. When you use the + operator with a string and a number, JavaScript converts the number to a string and performs concatenation instead of addition. To resolve this, you need to explicitly convert the input and cell value to numbers before adding them.

Here’s a revised version of your script that should work correctly:

function addPoints() {
  let ui = SpreadsheetApp.getUi();
  let input = ui.prompt('Enter points to add').getResponseText();
  
  let sheet = SpreadsheetApp.getActiveSheet();
  let cell = sheet.getActiveCell();
  let currentValue = cell.getValue();
  
  let result = parseFloat(input) + parseFloat(currentValue);
  cell.setValue(result);
}

This script uses parseFloat() to convert the input and cell value to numbers. It then performs the addition and sets the result back to the cell. This should give you the correct sum you’re looking for.

4

I ran into a similar issue when I first started scripting in Google Sheets. The problem is that the input from ui.prompt() is treated as a string, not a number. To fix this, you need to convert both the input and the current cell value to numbers before adding them.

Here’s a modified version of your script that should work:

function addPoints() {
  let ui = SpreadsheetApp.getUi();
  let input = ui.prompt('Enter points to add').getResponseText();
  
  let sheet = SpreadsheetApp.getActiveSheet();
  let cell = sheet.getActiveCell();
  let currentValue = cell.getValue();
  
  let numInput = Number(input);
  let numCurrentValue = Number(currentValue);
  
  cell.setValue(numInput + numCurrentValue);
}

This script uses the Number() function to convert the input and current cell value to numbers before adding them. This should solve your concatenation issue and give you the correct sum. Hope this helps!