Google Sheet Script: forEach and setValue (or setValues)

0
votes

I'm using forEach to iterate through an array of player names and add in the string ' plays soccer.' to the name. When I use Logger.log, everything appears as expected. Each player name is listed like 'Bob plays soccer.' or 'John plays soccer.'.

However, when I try to put it into a sheet, I only get the last name in the array.

How can I get each name in the list to show up in the sheet itself, specifically using forEach?

I know there are ways to use the standard for loop but I feel like I understand this syntax better whereas with the for loop I really don't understand how it works. Even trying to replicate it from what others have done doesn't make much sense to me. I'm sure I'm missing something dumb and simple, but I can't figure it out.

NOTE: Even when I change the "numRows" to 4, it just repeats "Tim plays soccer." four times. I want each name to show up once. 

function forEachTest (){

var players = ['Bob', 'John', 'Mark', 'Tim'];

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

var startRow = 1
var startColumn = 1
var numRows = 1
var numColumns = 1

var dataRange = sheet.getRange(startRow, startColumn, numRows, numColumns);

players.forEach(function(eachPlayer) {
Logger.log(eachPlayer + ' plays soccer.');
Logger.log(eachPlayer);

dataRange.setValue(eachPlayer + ' plays soccer.');

});
2
google-apps-scriptgoogle-sheets

2 Answers

0
votes

You need to set your range individualy, right now it writes the right names one by one on the 1st cell, and if you change numRow, it writes it on all the cells

Something like that:

var i = 1;
players.forEach(function(eachPlayer) {
    Logger.log(eachPlayer + ' plays soccer.');
    Logger.log(eachPlayer);

    dataRange = sheet.getRange(startRow + i, startColumn);
    dataRange.setValue(eachPlayer + ' plays soccer.');
    i++; //to get the next row
});

If you really don't want i, you could even use sheet.addRow to create a new empty row for each player.

0
votes

You are setting the value of your entire dataRange to the same value four times. You would need to iterate setting the value for each player like so:

function forEachTest (){

var players = ['Bob', 'John', 'Mark', 'Tim'];

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

var currentRow = 1;
var startColumn = 1;

players.forEach(function(eachPlayer) {
Logger.log(eachPlayer + ' plays soccer.');
Logger.log(eachPlayer);

sheet.getRange(currentRow, startColumn).setValue(eachPlayer + ' plays soccer.');
currentRow++;
});