Google Apps Scripts: Append an array of values as a new row to a Sheets

Walden Systems Geeks Corner News Google Apps Scripts: : Append an array of values as a new row to a Sheets Rutherford NJ New Jersey NYC New York City North Bergen County

Working with google sheets one quickly realizes there are many good reasons to automate many tasks. Even simple tasks like deleting a row(s) or inserting a row(s) get tedious, annoying and time consuming. In this blog we will go over a simple yet very useful function to automate the task of inserting rows and columns from an array of data.

When it comes to automation google apps script is a mature API and language based on javascript. It is not yet as versatile as Microsoft Excel, but it is darn close when it comes to features you are looking to automate in a spreadsheet.

I often work with data sets that are populated from a database or another spreadsheet to analyze big chunks of data. One function that comes in handy is to be able to build out my new spreadsheet with rows and columns already populated from a database or another datasource. Once I have my data in a spreadsheet I can kick off my code that runs analysis on the data.


Assuming we already have an array of data, I can easily add this array to the sheet by calling the 'sheet.appenRow()' function. We will keep the function signature simple, by passing two parameters, array of data and the sheet to be populated:


function appendRowWithValues(values, sheet);

Since the function appendRowWithValues()' will append the new row and columns on the last row of the sheet, we want our function to return the new row for future processing.

Putting it all together:


function appendRowWithValues(values, sheet) {
  var appnd2sheet = sheet.appendRow(values);
  var lastRow = appnd2sheet.getLastRow();
  return(lastRow);
};

function myMainBlog() {
  const sSheet = SpreadsheetApp.getActiveSpreadsheet();
  var firstSheet = sSheet.getSheets()[0];
  var valueArray = ["val-01", "val-02", "val-03"];
  var insertedRow = 0;
  insertedRow = appendRowWithValues(valueArray, firstSheet);
  Logger.log(insertedRow);
};