Is there a clean way to write a sparse grid to a google sheets range

Question

I am using Google sheets to store data that is updated from a web app.

I retrieve all the data by using getValues() on getDataRange() of the sheet, like this:

    // get reference to the data sheet
    var ss = _getPOMasterSpreadsheet();
    var shtData = ss.getSheetByName( "TestSheet11" );

    // obtain the grid
    var dataRange = shtData.getDataRange();
    var dataGrid = dataRange.getValues();

    var cols = dataGrid[0].length;
    var rows = dataGrid.length;

Sometimes I extend the the data, by adding new rows, but not every column in the row contains data, for example:

    // create a new row and add it to the grid
    var newRow = new Array( cols );
    dataGrid.push( newRow );

    // write a value to the last cell of the new row
    newRow[ newRow.length-1 ] = "NEW LAST CELL" ;

Then the newly extended grid is written back to the sheet using setValues()

    // get the range to be updated and set values from the grid
    var newDataRange = shtData.getRange(1, 1, rows + 1, cols);
    newDataRange.setValues( dataGrid );

This means that there are parts of the grid that have no value. When the grid is written to the sheet, the cells corresponding to undefined values are filled with the text "NOT_FOUND" instead of being blank:

Is there some way to get these undefined values to be empty cells when writing back to the sheet? I was trying to avoid having a loop to fill all the undefined grid locations with zero length strings.

Answer 1

as @I'-'I told me fill is not available in app script.

You still can probably use it at client side so here what you shoul write:

newRow.fill(""); // or 0

That being the case If you code on server side I don't see any other solutions than using a forEachfunction as so:

   var newRow = new Array( cols );
   newRow.forEach(function(element, index)
   {
     newRow[index] = ""; // or 0
   });
   newRow[ newRow.length - 1] = "NEW LAST CELL" ;
   dataGrid.push( newRow );

You can go have a look at this post

REFERENCES

array.forEach()