Google AppScript: Create and Write to Google SpreadSheet

This article shows how to create a new Google Sheet and write data into it with Google AppScript. We will also be writing data into existing Google SpreadSheet.

Create a new Google Sheet and write data into it with AppScript

The following function will create a Google Sheet named TestSheet in your Google Drive.
Then, it will add some text in the first three cells of the first row of the spreadsheet.
After that, it will freeze the first row, i.e. the first row will be non-editable.
Then, it will set the fontsize, background color and font color of the first row.

/**
 * SpreadSheet Classes Overview
 * https://developers.google.com/apps-script/reference/spreadsheet/
 *
 * SpreadSheetApp Class Reference 
 * https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app
 * 
 * Extending Google Sheets Guide
 * https://developers.google.com/apps-script/guides/sheets
 */

function createWriteSheet() {    
  var ss = SpreadsheetApp.create('TestSheet');
  var sheet = ss.getSheets()[0];
  
  // set value of first cell (1st row, 2nd column)
  sheet.getRange('A1').setValue('Name');
  
  // set value of (1st row, 2nd column) cell
  sheet.getRange('B1').setValue('Age');
  
  // set value of (1st row, 3rd column) cell
  sheet.getRange('C1').setValue('Email');
  
  // Freeze first row (making the row non-editable)
  sheet.setFrozenRows(1);
  
  // Set the font size, background color, font color for first row
  sheet.getRange('1:1').setFontSize(14);
  sheet.getRange('1:1').setBackground('#708090');
  sheet.getRange('1:1').setFontColor('white');   
}

Write data to Google Spreadsheet with AppScript

In the code below, a spreadsheet is opened by its id and then some data is added to that spreadsheet.

To get the spreadsheed id:

– Open Google Spreadsheet
– See the URL in your browser’s address bar
– The URL will be something like this: https://docs.google.com/spreadsheets/d/9tXNUNpM9tXNUNp0BydbYIIYLMxxxxxxxxxx/edit#gid=0
– So, in this case, the spreadsheet id is “9tXNUNpM9tXNUNp0BydbYIIYLMxxxxxxxxxx”

function writeSheet() {  
  var sheetId = '9tXNUNpM9tXNUNp0BydbYIIYLMxxxxxxxxxx'; // ID of spreadsheet  
  var data = 'Hello Nepal';
  var ss = SpreadsheetApp.openById(sheetId);
  
  var sheet = ss.getActiveSheet();
  //var sheet = ss.getSheetByName('Sheet1'); // Sheet1 is the name of the Sheet
  
  //var range = sheet.getRange('A3:C2'); // get range from A3 to C2
  
  // start from 2nd row, 2nd col and get range of 3 rows and 3 cols from there
  var range = sheet.getRange(2,2,3,3); 
  
  range.setValue(data);  
}

Hope this helps. Thanks.