Zac Henson Zac Henson - 1 month ago 9
Javascript Question

Google Apps Script - How to Get a row and combine the row and a text string into one new row

I am trying to get a range from one sheet and add the range plus a string to create a new row in a new sheet. The new row in the new sheet should have the string, then the row. I though maybe i could do this with an array but its not working.

//tradeName is the name of the source file. The code is super long so i did not include it in this snippet.

var fileList = DriveApp.getFilesByName(tradeName);
var fileId = fileList.next().getId();
var sheetCurrent = SpreadsheetApp.getActiveSheet();
var source = SpreadsheetApp.openById(fileId);
var source_range = source.getRange("A20:AE20");

sheetCurrent.appendRow([tradeName,A20:AE20]);

Answer

You can't mix ranges and string values. As you noticed, the argument in appendRow must be an array, so the right way to do what you want is to add the string to the array before adding the new row. On the other hand, when you retrieve a row value you get an array of arrays (a 2D array) so we have to take only the inner array and use the unshift method to add an element before the others.

Code could be like this :

function myFunction() {
  var string = " ccccxxxx";
  var sheetCurrent = SpreadsheetApp.getActiveSheet();
  var source = SpreadsheetApp.openById(fileId);
  var source_range = source.getRange("A20:AE20");
  var source_rangeValue = source_range.getValues(); // 2D ARRAY   
  source_rangeValue[0].unshift(string);//add the string to the inner array
  sheetCurrent.appendRow(source_rangeValue[0]);
}
Comments