SpreadsheetApp

Open a spreadsheet

function openSpreadsheet() {
  // The code below opens a spreadsheet using its URL and logs the name for it.
  // Note that the spreadsheet is NOT physically opened on the client side.
  // It is opened on the server only (for modification by the script).
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  Logger.log(ss.getName());
}

Add data validation rule

function createValidationRule() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  // Name of the specific sheet in the spreadsheet.
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  var cell = sheet.getRange('A1');
  var rule = SpreadsheetApp.newDataValidation()
     .requireNumberBetween(1, 100)
     .setAllowInvalid(false)
     .setHelpText('Number must be between 1 and 100.')
     .build();
  cell.setDataValidation(rule);
}

Append rows to a spreadsheet

function appendARow() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  // Name of the specific sheet in the spreadsheet.
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  // Appends a new row with 3 columns to the bottom of the
  // spreadsheet containing the values in the array.
  sheet.appendRow(['a man', 'a plan', 'panama']);
}

Add a line chart

function addNewChart() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  // Name of the specific sheet in the spreadsheet.
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  // Creates a line chart for values in range A2:B8.
  var range = sheet.getRange('A2:B8');

  var chartBuilder = sheet.newChart();
  chartBuilder.addRange(range)
     .setChartType(Charts.ChartType.LINE)
     .setOption('title', 'My Line Chart!');
  sheet.insertChart(chartBuilder.build());
}

Clear spreadsheet content while preserving any formatting

function clearSheetData() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  // Name of the specific sheet in the spreadsheet.
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);
  sheet.clearContents();
}

Clear spreadsheet formatting while preserving any data

function clearSheetFormatting() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  // Name of the specific sheet in the spreadsheet.
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  sheet.clearFormats();
}

Copy data to cell range

function copyData() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  // Name of the specific sheet in the spreadsheet.
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  // The code below will copy the first 5 columns over to the 6th column.
  var rangeToCopy = sheet.getRange(1, 1, sheet.getMaxRows(), 5);
  rangeToCopy.copyTo(sheet.getRange(1, 6));
}

Copy formatting to cell range

function copyFormatting() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  var SOURCE_SHEET_NAME = 'INSERT_SHEET_NAME_HERE';
  var DESTINATION_SHEET_NAME = 'INSERT_SHEET_NAME_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sourceSheet = ss.getSheetByName(SOURCE_SHEET_NAME);
  var destinationSheet = ss.getSheetByName(DESTINATION_SHEET_NAME);

  var range = sourceSheet.getRange('B2:D4');

  // This copies the formatting in B2:D4 in the source sheet to
  // D4:F6 in the destination sheet.
  range.copyFormatToRange(destinationSheet, 4, 6, 4, 6);
}

Get the last cell on a spreadsheet in which data is present

function getLastCellWithData() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  // Name of the specific sheet in the spreadsheet.
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  // Log the last cell with data in it, and its co-ordinates.
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  var lastCell = sheet.getRange(lastRow, lastColumn);
  Logger.log('Last cell is at (%s,%s) and has value "%s".', lastRow, lastColumn,
      lastCell.getValue());
}

Insert image in a spreadsheet

function insertImageOnSpreadsheet() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  // Name of the specific sheet in the spreadsheet.
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  var response = UrlFetchApp.fetch(
      'https://developers.google.com/adwords/scripts/images/reports.png');
  var binaryData = response.getContent();

  // Insert the image in cell A1.
  var blob = Utilities.newBlob(binaryData, 'image/png', 'MyImageName');
  sheet.insertImage(blob, 1, 1);
}

Make a copy of a spreadsheet

function copyASpreadsheet() {
  // This code makes a copy of the current spreadsheet and names it
  // appropriately.
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

  var newSpreadsheet = ss.copy('Copy of ' + ss.getName());
  Logger.log('New spreadsheet URL: %s.', newSpreadsheet.getUrl());
}

Log the data of a spreadsheet

function getAllValuesOnSpreadsheet() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  // Name of the specific sheet in the spreadsheet.
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  // This represents ALL the data.
  var range = sheet.getDataRange();
  var values = range.getValues();

  // This logs the spreadsheet in CSV format.
  for (var i = 0; i < values.length; i++) {
    Logger.log(values[i].join(','));
  }
}

Retrieve a named range from a spreadsheet

function getNamedRange() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

  // Log the number of columns for the range named 'TaxRates' in the
  // spreadsheet.
  var range = ss.getRangeByName('TaxRates');
  if (range) {
    Logger.log(range.getNumColumns());
  }
}

Set cell formula

function setCellFormula() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  // Name of the specific sheet in the spreadsheet.
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  // Sets formula for cell B5 to be sum of values in cells B3 and B4.
  var cell = sheet.getRange('B5');
  cell.setFormula('=SUM(B3:B4)');
}

Set cell number format

function setNumberFormats() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  // Name of the specific sheet in the spreadsheet.
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  var cell = sheet.getRange('B2');

  // Always show 3 decimal points.
  cell.setNumberFormat('0.000');
}

Set a range's values

function setCellValues() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  // Name of the specific sheet in the spreadsheet.
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  // The size of the two-dimensional array must match the size of the range.
  var values = [
    ['2.000', '1,000,000', '$2.99']
  ];

  var range = sheet.getRange('B2:D2');
  range.setValues(values);
}

Sort a range of values by multiple columns

function sortARangeOfValues() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  // Name of the specific sheet in the spreadsheet.
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  var range = sheet.getRange('A1:C7');

  // Sorts descending by column B, then ascending by column A
  // Note the use of an array
  range.sort([{column: 2, ascending: false}, {column: 1, ascending: true}]);
}

Sort a spreadsheet by a specified column

function sortSheet() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  // Name of the specific sheet in the spreadsheet.
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  // Sorts the sheet by the first column, descending.
  sheet.sort(1, false);
}

Update data validation rules

function updateDataValidationRules() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  // Name of the specific sheet in the spreadsheet.
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  // Change existing data-validation rules that require a date in 2013 to
  // require a date in 2014.
  var oldDates = [new Date('1/1/2013'), new Date('12/31/2013')];
  var newDates = [new Date('1/1/2014'), new Date('12/31/2014')];

  var range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
  var rules = range.getDataValidations();

  for (var i = 0; i < rules.length; i++) {
    for (var j = 0; j < rules[i].length; j++) {
      var rule = rules[i][j];

      if (rule) {
        var criteria = rule.getCriteriaType();
        var args = rule.getCriteriaValues();

        if (criteria == SpreadsheetApp.DataValidationCriteria.DATE_BETWEEN &&
            args[0].getTime() == oldDates[0].getTime() &&
            args[1].getTime() == oldDates[1].getTime()) {
          // Create a builder from the existing rule, then change the dates.
          rules[i][j] = rule.copy().withCriteria(criteria, newDates).build();
        }
      }
    }
  }
  range.setDataValidations(rules);
}

Send feedback about...

AdWords Scripts
AdWords Scripts
Need help? Visit our support page.