CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api
THYMOLE .
THYMOLE .

Reputation: 53

Simple Google Spreadsheet For Each Loop?

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.

Upvotes: 4

Views: 27175

Answers (1)

user6655984
user6655984

Reputation:

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.

Upvotes: 3

Related Questions