4

It's been a long time since I've coded. I've never looked into scripts for google spreadsheets before. I just want to make a simple effect to edit the spreadsheet. If I understand correctly, this is doable so long as you run it manually?

The syntax is throwing me off too much. My basic goal is to set each cell in a fixed column range to equal itself plus the value in the adjacent column, and then set that second value to 0.

My instinct would be to do something such as

CellRange[i][j] selected = C9:D13;
for(i=0,i<selectedrange.length,i++){
SpreadsheetApp.getActiveRange().setValue(selected[i][j]+selected[i][j+1];
SpreadsheetApp.getRange(selected[i][j+1]).setValue(0);
}

That's probably terribly wrong but I feel I ought to at least throw my best guess out before asking for help.

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
THYMOLE .
  • 53
  • 1
  • 1
  • 3
  • 1
    Google-apps-script is java script. There are examples on the web. Good luck. – eddyparkinson Aug 21 '13 at 03:09
  • This other question sounds quite similar, and the answer may be close to what you're looking for. http://stackoverflow.com/questions/15616530/google-spreadsheet-script-how-to-loop-range-of-cells-and-set-value-in-adjacent – stmcallister Aug 21 '13 at 06:05

1 Answers1

3

Say, the goal is to process the range C9:D13 by adding the value in D to the value in C, and then setting D to zero. The code would look like this:

function addColumns() {
  var sheet = SpreadsheetApp.getActiveSheet(); // or getSheetByName
  var range = sheet.getRange("C9:D13");
  var values = range.getValues();
  for (var i = 0; i < values.length; i++) {
    values[i][0] = values[i][0] + values[i][1];
    values[i][1] = 0;
  }
  range.setValues(values);
}

Here, values[i][0] means values the first column in the range we're working on (namely C) and values[i][1] refers to the second. The indexing of JavaScript arrays begins with 0, unlike the numbering of rows in spreadsheets.