CODEWITHSUNDEEP
javascriptgoogle-sheets-api
Oday Salim
Oday Salim

Reputation: 1147

Googlesheets script - add incrementing numbers that repeat

I am new here. I need support with my code that I use for Googlesheets.

Background:

I am looking to create 1 to 12 in column A and repeat over and over again; something like this: 1, 2, 3, [...], 12, 1, 2, [...], 12, and so on.

Code:

I have written this code, but I am not sure where I am going wrong. Also not sure how to make it repeat after 12 every time. Help would be appreciated.

var numRows = range.getNumRows();
for (var i = 1; i <= numRows; i++) {
var currentValue = sheet.getCell(i,1).getValue();
var withString = 0;
range.getCell(i,1).setValue(val + 1);
}

Upvotes: 2

Views: 48

Answers (2)

Martin Da Fonte
Martin Da Fonte

Reputation: 56

Using modulo will help on getting the numbers from 1 to 12.

One change you could make to your code, based on Google recomendations, is to update the data on the spreadhseet on batch operations. So instead of changing every cell one by one you could change the values for the whole range.

Example:

function addValuesToRows() {   
   var range = SpreadsheetApp.getActive().getActiveSheet()
       .getRange(1, 1, 100);   
   var numRows = range.getNumRows();   
   var values = range.getValues();   
   for (var i = 0; i < numRows; i++) {
      values[i][0] = (i % 12) + 1;  
   }   
   range.setValues(values); //Setting the value to the whole range
}

Upvotes: 0

Matt Fletcher
Matt Fletcher

Reputation: 9240

Have a look into modulo functions.

Think of it like a clock, where the values increase until they reach the modulo, at which point they start "looping". You'll want to print the value of i % 12, but modified because it's not zero indexed, so more like ((i - 1) % 12) + 1.

for (var i = 1; i < 36; i++) {
  console.log(i, ((i - 1) % 12) + 1);
}

Result:

1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 1
14 2
15 3
16 4
17 5
18 6
19 7
20 8
21 9
22 10
23 11
24 12
25 1
26 2
27 3
// etc etc

Upvotes: 1

Related Questions