SpreadsheetApp

function openSpreadsheet(spreadsheetUrl) {   // 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).   const ss = SpreadsheetApp.openByUrl(spreadsheetUrl);   console.log(ss.getName());   return ss; }

function createValidationRule() {   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   // Name of the specific sheet in the spreadsheet.   const SHEET_NAME = 'INSERT_SHEET_NAME_HERE';    const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);   const sheet = ss.getSheetByName(SHEET_NAME);    const cell = sheet.getRange('A1');   const rule = SpreadsheetApp.newDataValidation()      .requireNumberBetween(1, 100)      .setAllowInvalid(false)      .setHelpText('Number must be between 1 and 100.')      .build();   cell.setDataValidation(rule); }

function appendARow() {   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   // Name of the specific sheet in the spreadsheet.   const SHEET_NAME = 'INSERT_SHEET_NAME_HERE';    const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);   const 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']); }

function addNewChart() {   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   // Name of the specific sheet in the spreadsheet.   const SHEET_NAME = 'INSERT_SHEET_NAME_HERE';    const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);   const sheet = ss.getSheetByName(SHEET_NAME);    // Creates a line chart for values in range A2:B8.   const range = sheet.getRange('A2:B8');    const chartBuilder = sheet.newChart();   chartBuilder.addRange(range)      .setChartType(Charts.ChartType.LINE)      .setOption('title', 'My Line Chart!');   sheet.insertChart(chartBuilder.build()); }

function clearSheetData() {   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   // Name of the specific sheet in the spreadsheet.   const SHEET_NAME = 'INSERT_SHEET_NAME_HERE';    const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);   const sheet = ss.getSheetByName(SHEET_NAME);   sheet.clearContents(); }

function clearSheetFormatting() {   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   // Name of the specific sheet in the spreadsheet.   const SHEET_NAME = 'INSERT_SHEET_NAME_HERE';    const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);   const sheet = ss.getSheetByName(SHEET_NAME);    sheet.clearFormats(); }

function copyData() {   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   // Name of the specific sheet in the spreadsheet.   const SHEET_NAME = 'INSERT_SHEET_NAME_HERE';    const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);   const sheet = ss.getSheetByName(SHEET_NAME);    // The code below will copy the first 5 columns over to the 6th column.   const rangeToCopy = sheet.getRange(1, 1, sheet.getMaxRows(), 5);   rangeToCopy.copyTo(sheet.getRange(1, 6)); }

function copyFormatting() {   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   const SOURCE_SHEET_NAME = 'INSERT_SHEET_NAME_HERE';   const DESTINATION_SHEET_NAME = 'INSERT_SHEET_NAME_HERE';    const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);   const sourceSheet = ss.getSheetByName(SOURCE_SHEET_NAME);   const destinationSheet = ss.getSheetByName(DESTINATION_SHEET_NAME);    const 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); }

function getLastCellWithData() {   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   // Name of the specific sheet in the spreadsheet.   const SHEET_NAME = 'INSERT_SHEET_NAME_HERE';    const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);   const sheet = ss.getSheetByName(SHEET_NAME);    // Log the last cell with data in it, and its co-ordinates.   const lastRow = sheet.getLastRow();   const lastColumn = sheet.getLastColumn();   const lastCell = sheet.getRange(lastRow, lastColumn);   console.log('Last cell is at (%s,%s) and has value "%s".', lastRow, lastColumn,       lastCell.getValue()); }

function insertImageOnSpreadsheet() {   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   // Name of the specific sheet in the spreadsheet.   const SHEET_NAME = 'INSERT_SHEET_NAME_HERE';    const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);   const sheet = ss.getSheetByName(SHEET_NAME);    const response = UrlFetchApp.fetch(       'https://developers.google.com/google-ads/scripts/images/reports.png');   const binaryData = response.getContent();    // Insert the image in cell A1.   const blob = Utilities.newBlob(binaryData, 'image/png', 'MyImageName');   sheet.insertImage(blob, 1, 1); }

function copyASpreadsheet() {   // This code makes a copy of the current spreadsheet and names it   // appropriately.   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);    const newSpreadsheet = ss.copy('Copy of ' + ss.getName());   console.log('New spreadsheet URL: %s.', newSpreadsheet.getUrl()); }

function getAllValuesOnSpreadsheet() {   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   // Name of the specific sheet in the spreadsheet.   const SHEET_NAME = 'INSERT_SHEET_NAME_HERE';    const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);   const sheet = ss.getSheetByName(SHEET_NAME);    // This represents ALL the data.   const range = sheet.getDataRange();   const values = range.getValues();    // This logs the spreadsheet in CSV format.   for (let i = 0; i < values.length; i++) {     console.log(values[i].join(','));   } }

function getNamedRange() {   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);    // Log the number of columns for the range named 'TaxRates' in the   // spreadsheet.   const range = ss.getRangeByName('TaxRates');   if (range) {     console.log(range.getNumColumns());   } }

function setCellFormula() {   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   // Name of the specific sheet in the spreadsheet.   const SHEET_NAME = 'INSERT_SHEET_NAME_HERE';    const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);   const sheet = ss.getSheetByName(SHEET_NAME);    // Sets formula for cell B5 to be sum of values in cells B3 and B4.   const cell = sheet.getRange('B5');   cell.setFormula('=SUM(B3:B4)'); }

function setNumberFormats() {   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   // Name of the specific sheet in the spreadsheet.   const SHEET_NAME = 'INSERT_SHEET_NAME_HERE';    const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);   const sheet = ss.getSheetByName(SHEET_NAME);    const cell = sheet.getRange('B2');    // Always show 3 decimal points.   cell.setNumberFormat('0.000'); }

function setCellValues() {   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   // Name of the specific sheet in the spreadsheet.   const SHEET_NAME = 'INSERT_SHEET_NAME_HERE';    const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);   const sheet = ss.getSheetByName(SHEET_NAME);    // The size of the two-dimensional array must match the size of the range.   const values = [     ['2.000', '1,000,000', '$2.99']   ];    const range = sheet.getRange('B2:D2');   range.setValues(values); }

function sortARangeOfValues() {   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   // Name of the specific sheet in the spreadsheet.   const SHEET_NAME = 'INSERT_SHEET_NAME_HERE';    const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);   const sheet = ss.getSheetByName(SHEET_NAME);    const 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}]); }

function sortSheet() {   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   // Name of the specific sheet in the spreadsheet.   const SHEET_NAME = 'INSERT_SHEET_NAME_HERE';    const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);   const sheet = ss.getSheetByName(SHEET_NAME);    // Sorts the sheet by the first column, descending.   sheet.sort(1, false); }

function updateDataValidationRules() {   const SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';   // Name of the specific sheet in the spreadsheet.   const SHEET_NAME = 'INSERT_SHEET_NAME_HERE';    const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);   const sheet = ss.getSheetByName(SHEET_NAME);    // Change existing data-validation rules that require a date in 2013 to   // require a date in 2014.   const oldDates = [new Date('1/1/2013'), new Date('12/31/2013')];   const newDates = [new Date('1/1/2014'), new Date('12/31/2014')];    const range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());   const rules = range.getDataValidations();    for (let i = 0; i < rules.length; i++) {     for (let j = 0; j < rules[i].length; j++) {       const rule = rules[i][j];        if (rule) {         const criteria = rule.getCriteriaType();         const 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); }