Construct a range of values to overwrite a row in a Sheet.

[Greg Janusz]

In Script, I am constructing a list of values that, when done, I want to assign to a newly inserted row.

// Create an array to hold the new row's values

  activeSheet.insertRowAfter(lastRowNum);

  var newRowData = Array(lastColNum);

  var newRowNum = lastRowNum + 1;

  newRowData[0] = "(New Row, first cell)";

// ...

// Fills in more elements of newRowData

// ...

// Overwrite row # newRowNum with new data (to avoid doing it one cell at a time)

activeSheet.getRange(newRowNum, 1).setValues(newRowData);

However, it appears that I cannot assign a row of cells from an Array. I don't know how to create an empty range of the right length, which would be 1 row of lastColNum elements.

How should I actually handle this?

[Halicate]

Hi Greg, try this:

  // Create an array to hold the new row's values

  //activeSheet.insertRowAfter(lastRowNum);

  var newRowData = Array(lastColNum);

  var newRowNum = lastRowNum + 1;

  newRowData[0] = "(New Row, first cell)";

// ...

// Fills in more elements of newRowData

// ...

// Overwrite row # newRowNum with new data (to avoid doing it one cell at a time)

//activeSheet.getRange(newRowNum, 1).setValues(newRowData);

activeSheet.appendRow(newRowData);

You also can substitute "lastColNum" by "activeSheet.getLastColumn()" and forget newRowNum and lastRowNum

Regards,

[cwl...@gmail.com]

Greg, to set a full row at once, Sheets is finicky and very precise. If your new row contains say 12 cells (columns), you have to tell sheets exactly which 12 cells you want your array to go into.  So something like:
activeSheet.getRange(1, 1, 1,  newRowData.length).setValues(newRowData);
This assumes newRowData is a one-dimensional array  [ cell1, cell2, etc]  and not 2-d [ [ cell1, cell2, etc] ]. 

I use this snippet to write CSV data directly to a new sheet. 

(data.length gives me the number of rows, and the first row (data[0].length) gives me the number of columns.)

...............

    var data = Utilities.parseCsv(file.getBlob().getDataAsString());

    var ss = SpreadsheetApp.openById(SHEETID);

    sheet = ss.getSheetByName("Sheet1");

    await sheet.getDataRange().clearContent();

    SpreadsheetApp.flush();

    sheet.getRange(1, 1, data.length, data[0].length).setValues(data);  //start at top left row1, col1

................

[cwl...@gmail.com]

OOps, wasn't paying attention.  It should NOT be row 1, but the newly inserted row:
activeSheet.getRange( activeSheet.getLastRow() +1 , 1, 1,  newRowData.length).setValues(newRowData);

[Greg Janusz]

Thank you!

[Beth Olmo]

I know this is almost a year old, but here's hoping someone sees it & has a good idea...

What if newRowData is a multidimensional array? We're bringing data in from an html form, and the last item in our array needs to accept multiple values and display in one cell in the sheet.

On Saturday, February 4, 2023 at 7:21:09 AM UTC-5 cwl...@gmail.com wrote:

[Keith Andersen]

Beth, 

Off the top of my head, you would need to loop through each row of the 2 dimensional array and thake the last element (which itself is an array) in that row array and change that array to a string then push it to the temp array. The temp array then would be your original array with the last element to string and would populate in a single cell.

How many rows are you talking about?

Keith 

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b55255bb-ccaf-475b-8907-c40f24b03826n%40googlegroups.com.

[Keith Andersen]

This works:

function myFunction() {
 
  let data = [
    ["a", "b", "c", "d", [1,2,3,4]],
    ["e", "f", "g", "h", [5,6,7,8]],
    ["i", "j", "k", "l", [9,0,0,0]]
    ];

  for(let i = 0; i < data.length; i++){
    let tempRow = data[i];
    let newString = data[i][4].toString()//.replace(",", " ");//---turns last array element (an array) to string
    tempRow.pop();//---removes the last element
    tempRow.push(newString);//--adds new last element as a string
   
    //---writes to row each iteration of the loop
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(i + 1,1, 1, 5).setValues([tempRow]);
  }
}

[Keith Andersen]

Sorry Beth, I noticed a needed correction. Use this one.

function myFunction() {

let data = [

["a", "b", "c", "d", [1,2,3,4]],

["e", "f", "g", "h", [5,6,7,8]],

["i", "j", "k", "l", [9,0,0,0]]

];

for(let i = 0; i < data.length; i++){

let tempRow = data[i];

let newString = data[i][4].toString();//---turns last array element (an array) to string

[Keith Andersen]

What the heck, Gmail split that text. lets try again;

function myFunction() {
 
  let data = [
    ["a", "b", "c", "d", [1,2,3,4]],
    ["e", "f", "g", "h", [5,6,7,8]],
    ["i", "j", "k", "l", [9,0,0,0]]
    ];

  for(let i = 0; i < data.length; i++){
    let tempRow = data[i];
    let newString = data[i][4].toString();//---turns last array element (an array) to string
    tempRow.pop();//---removes the last element
    tempRow.push(newString);//--adds new last element as a string
   
    //---writes to row each iteration of the loop
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(i + 1,1, 1, 5).setValues([tempRow]);
  }
}

Finally! 😃👍

[Keith Andersen]

Did that help?

[Beth Olmo]

Hi Keith,

Sorry for the delay - thanks so much for all this! I'm saving it for later reference.

My supervisor forced the issue with JS on our site rather than working any further in the Apps Script.

[Keith Andersen]

Thank you 👍

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/dc981c8d-3890-44a6-95ce-1a40b2e3c262n%40googlegroups.com.