CODEWITHSUNDEEP
for-loopmultidimensional-arraygoogle-apps-scriptgoogle-sheets
Alex Jackson
Alex Jackson

Reputation: 5

How to add a 2D array several times at the end of an existing 2D array using a for loop?

Starting point (boundary conditions):

Goal:
Running the script should add A5 number of weeks starting from the date A6 to the "upcoming events" section (row 22 and following) with the correct date. This is how it would look like after the script ran successfully:

What works so far:
The script is able to add the recurring events for one week in the right order to the upcoming events section. It works as well if the starting date is in the middle of the week. (Not shown here as it is probably not relevant.)

My code:

function recurringWeeks() {
  var ss = SpreadsheetApp.getActiveSheet(); // selects the active spreadsheet
  var repeatingWeeks = ss.getRange(5,1).getValue(); // gets how many weeks it should repeat
  var regWeek = ss.getRange(9, 2, 3, 7).getValues(); // gets the regular week data
  var regWeekRepeated = ss.getRange(9, 2, repeatingWeeks*3, 7); // create an array to store the events for all weeks

  // fill regWeekRepeated with regWeek
  for (var j = 0; j < repeatingWeeks; j++){
    for (var i = 0; i < 3; i++){
      regWeekRepeated[i+j*3] = regWeek[i];
  }
  }  

  // Repeat week for "A5" times and add to start/end date a week
  for (var j = 0; j < repeatingWeeks; j++){
    for (var i = 0; i < 3; i++){
    regWeekRepeated[i+j*3][0] = new Date(regWeek[i][0].getTime() + j*7*3600000*24); // <-This line leads to an error message.
    }
  }

  ss.getRange(ss.getLastRow()+1,2,repeatingWeeks*3,7).setValues(regWeekRepeated); // copies weekly events after the last row
}

Edit of [i+j*6] to [i+j*3] in Repeat week for "A5" times and add to start/end date a week

Approach:
As I have solved how to add one week of recurring events with the correct date and right order, I use this as my "point of attack". I'm pretty sure that a for-loop does the job and this is currently my preferred tool.

Best hit I've found in the search results: creating 2 dimension arrays However, this uses .push and as far as I understand this means an element (can be a row) is placed at the end of an array. I've tried to use push as well but have not been successful yet.

Questions:

A demo version of the spreadsheet

Update V01:
Changes: regWeekRepeated is now an array.
I've changed the for loop due to the feedback I've received.

  // fill regWeekRepeated with regWeek
  var regWeekRepeated = [];
  for (var j = 0; j < repeatingWeeks; j++){
    for (var i = 0; i < 3; i++){
      regWeekRepeated.push(regWeek[i]);
    }
  }
  Logger.log(regWeekRepeated)

Update V02: 

  // Repeat week for "A5" times and add to start/end date a week
  for (var j = 0; j < repeatingWeeks; j++){
    for (var i = 0; i < 3; i++){
    regWeekRepeated[i+j*3][0] = new Date(regWeek[i][0].getTime() + j*7*3600000*24); //adds a week to the dates for each cycle
    //Logger.log(regWeekRepeated[i]); // log is as expected and desired
    }
    Logger.log(regWeekRepeated); // second part of log not as expected.
  }
  //Logger.log(regWeekRepeated);
  ss.getRange(ss.getLastRow()+1,2,repeatingWeeks*3,7).setValues(regWeekRepeated); // copies weekly events after the last row

Here the log output placed in the "outer" for loop. 1 represents the first cycle, 2 the second cycle It looks like the second for loop overwrites the elements 0 to 2.

And here the output in google sheets

Update V03:
This makes sure that the changes don't affect the copy.

  // fill regWeekRepeated with regWeek
  for (var j = 0; j < repeatingWeeks; j++){
    for (var i = 0; i < 3; i++){
      regWeekRepeated[i+j*3] = regWeek[i].slice(); // shallow copy of an array
  }
  }

Upvotes: 0

Views: 113

Answers (2)

Wicket
Wicket

Reputation: 38140

Making scripts for spreadsheets could be tricked because the spreadsheet and JavaScript jargons/lexics use the same terms in different ways. Perhaps this is what is happening here.

  • ERROR 1: Why is it not possible to assign the value of an element from array regWeek to array regWeekRepeated?

regWeekRepeated is a Range object not a JavaScript Array

  • ERROR 2: Is this property issue related to ERROR 1 or something different? I've tried to solve both errors individually.

Yes it's related. See the previous answer.

  • Which approach makes more sense (logically or performance wise) in this context: push individual rows at the end of an existing array or use the whole week as array building blocks?

We could say that calling Google Apps Scripts classes and methods are "expensive" so we should try to minimize the number calls to these kind of elements. One way to do this is by passing the range values to a JavaScript Array , then make all the changes directly to it and we finish pass the resulting values to the corresponding range.

To pass the values of a range to a JavaScript 2D array, use range.getValues() and to pass the JavaScript 2D array values to a range use range.setValues().

Upvotes: 0

TheMaster
TheMaster

Reputation: 50445

regWeekRepeated is not a array. getRange() doesn't return a array.

Try changing from 

var regWeekRepeated = ss.getRange(9, 2, repeatingWeeks*3, 7); // create an array to store the events for all weeks

To

var regWeekRepeated = ss.getRange(9, 2, repeatingWeeks*3, 7).getValues(); // create an array to store the events for all weeks

Creating a array without touching the spreadsheet will increase performance.

var regWeekRepeated =[];

Upvotes: 1

Related Questions