-1

I want to make the below mentioned code working but it doesn't - nothing happens when I run it (also no error), that means the username (sUserName) doesn't get saved in the spreadsheet... And also I don't understand why the columns cant start with 2, 3, 4 (then the timestamp can stay in column #1) instead 1, 2, 3 - if so I get an error.

Here is the code:

 var userNameColumn = 1; //Column where the user name is written 
 var subTypeColumn = 2; //Column where the submitter type is written ex. "Requester"
 var sUserNameColumn = 3; //Column where the user name is saved

function saveUserName() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
 var rows  = sheet.getDataRange();
 var numRows = rows.getNumRows();

for (var i = 1; i <= numRows; i++) {

 var userNameCell = rows.getCell(i, userNameColumn);
 var subTypeCell = rows.getCell(i, subTypeColumn);
 var sUserNameCell = rows.getCell(i, sUserNameColumn);

  if(sUserNameCell.isBlank() && subTypeCell.getValue() === 'Requester') {
sUserNameCell.setValue(userNameCell)
  };  

 }
}

Here is the link for my spreadsheet and code:

Google Spreadsheet

pnuts
  • 58,317
  • 11
  • 87
  • 139
Christian Nagelbach
  • 1
  • 2

1 Answers1

1

See if this helps

function saveUserName() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
var rows = sheet.getDataRange().getValues();
for (var i = 1; i < rows.length; i++) {
    var userNameCell = rows[i][1];
    var subTypeCell = rows[i][2];
    var sUserNameCell = rows[i][3];
    if (!sUserNameCell && subTypeCell === 'Requester') sheet.getRange(i+1,4).setValue(userNameCell)
    }
}
JPV
  • 26,499
  • 4
  • 33
  • 48
  • `i` should start at 0. Also you could change the values in the array and then write the entire array back to the spreadsheet at the end so you only call `setValues` once. – Tesseract Oct 11 '15 at 13:08
  • Also there - no error, but also no entry in the 3rd column "Requester name" (sUserName)... But why? I dont understand :-( – Christian Nagelbach Oct 11 '15 at 13:55
  • So you have timestamp in col A, username in col B, type in col C. Doesn't that mean you want the requestername in col D (4) ? Keep in mind that arrays are zero-indexed. so col C (3) would be indexed as 2.Can you share a copy of your spreadsheet so we can see how your data is structured ? – JPV Oct 11 '15 at 15:40
  • To share the spreasheet i need your e-mail, please let me know - there is also an 2. coding which should work together with that one - would be great if you can repair it ;-) Thanks in advance! – Christian Nagelbach Oct 11 '15 at 16:20
  • I can't access that sheet. Can you share with editing rights to anyone with the link ? – JPV Oct 14 '15 at 07:08