Google App Script – Dynamically Set Spreadsheet Header Record

Say you’re creating a spreadsheet in Google Sheets that you intend to populate dynamically with order data from an eCommerce site with a Google App Script. You need to create a header record with the field names in it. You might have something like this:

function setupSheet() {

  ss.getRange('A1').setValue('Order Number');
  ss.getRange('B1').setValue('Status');
  ss.getRange('C1').setValue('Date Created');
  ss.getRange('D1').setValue('Refunded Date');
  ss.getRange('E1').setValue('Effective Date');
  ss.getRange('F1').setValue('Shipping Method');
  ss.getRange('G1').setValue('Shipping Total');
  ss.getRange('H1').setValue('Order Total');
  ss.getRange('I1').setValue('Refunded Amount');
  ss.getRange('J1').setValue('Grand Total');  
  ss.getRange('K1').setValue('Customer ID');
  ss.getRange('L1').setValue('Billing First Name');
  ss.getRange('M1').setValue('Billing Last Name');
  ss.getRange('N1').setValue('Billing email');
  ss.getRange('O1').setValue('Ordered Items');
  ss.getRange('P1').setValue('Refund Info');
   
  ss.getRange('A1:P1').setFontWeight('bold');
  ss.getRange('A1:P1').setFontStyle('italic');
  ss.setFrozenRows(1);
 
}

This script is setting the header record (each cell in the first row from column A to column P) to contain a preset label, styling it a bit and setting the top row to be frozen.

It’s fine the way it is, but if you decide the entire sheet would be better if it had another date value in the cell B1, you’d have to go back and change all the cell values from B1 to the end (now Q1) – which is time consuming and taking your mind off programming! It’s even more time consuming when you do it over and over again because you’re prototyping something and haven’t quite decided where you want to put everything.

Doing it Dynamically

A more dynamic way to do it is to put the cell addresses in one array and the labels in another, and loop through the labels setting the getRange statements dynamically:

function setupSheet() {

  columns = ['A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1','M1','N1','O1','P1','Q1','R1','S1','T1','U1','V1','W1',]; // you can add extra values here so that you don't run out when adding new labels

  column_titles = [
    'Order Number',
    'Status',
    'Date Created',
    'Refunded Date',
    'Effective Date',
    'Shipping Method',
    'Shipping Total',
    'Order Total',
    'Refunded Amount',
    'Grand Total',
    'Cost including Fees',
    'Fees',
    'Customer ID',
    'Billing First Name',
    'Billing Last Name',
    'Billing email',
    'Ordered Items',
    'Refund Info',
  ];

  column_titles.forEach( (c,idx) => { // loop through labels array and set the column headers based on the index
    ss.getRange(columns[idx]).setValue(c); // idx for columns and column_titles match
  });
   
  ss.getRange('1:1').setFontWeight('bold'); // set styles for the whole row ('1:1')
  ss.getRange('1:1').setFontStyle('italic');
  ss.setFrozenRows(1);
 
}

Now every time you want to either add a label, or rearrange the label order, you can just edit it in the column_titles array and the rest will do itself – no need to reset cell addresses. Just make sure the columns array is longer than the column_titles array so that you have enough columns to go round.

Additionally, you can do the styling on everything in the header record using ‘1:1’ which is the selector for the entire first row.