CODEWITHSUNDEEP
google-apps-scriptarrayofarrays
Adam Kruzics
Adam Kruzics

Reputation: 41

Google-Apps-Script can not write back a database with getDataRange().setValues()

I have a database loaded from a google spreadsheet:

mydatabase = sheet.getDataRange().getValues()

which then I extend with a new record:

mydatabase.push(mydatabase[x])

and then at the end of the script, I would want to write back the entire database to the google spreadsheet but the

sheet.getDataRange().setValues(mydatabase)

gives me ERROR since the new database is one record higher than the original was when loaded.

Is there any way to force the getDataRange() to write back the database into the sheet? The spreadsheet otherwise would have enough rows to accommodate the bigger dataset.

Upvotes: 2

Views: 324

Answers (1)

tehhowch
tehhowch

Reputation: 9872

In general, for .setValues(obj[][]) to work as expected, the Range that it is acting on must be the same size as the obj[][].

Commonly, this is ensured by acquiring a new Range from the desired Sheet:

var ss = SpreadsheetApp.openById("some id");
var sheet = ss.getSheetByName("some name");
var db = sheet.getDataRange().getValues();
/*
 * Do some operations on the obj[][] that is db
 * these operations can include adding / removing rows or columns.
 */
// If db is smaller than existing "written" data, the existing data should be cleared first.
if(db.length < sheet.getLastRow() || db[0].length < sheet.getLastColumn())
  sheet.clearContent();
// Write the current db back to the sheet, after acquiring the
// exact number of rows and columns needed to hold the values.
sheet.getRange(1, 1, db.length, db[0].length).setValues(db);

Upvotes: 1

Related Questions