Page Summary
-
Learn how to open a spreadsheet using its URL and access specific sheets within it.
-
Explore methods for manipulating cell data, including setting values, formulas, and number formats.
-
Discover how to manage data validation rules by creating and updating them.
-
Understand how to add, clear, and copy data and formatting within a spreadsheet.
-
Find out how to append rows, insert charts and images, and retrieve specific information like the last cell with data or named ranges.
-
Learn how to copy an entire spreadsheet and log all its data.
-
See examples of sorting data within ranges and entire sheets.
Open a spreadsheet
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 ; }
Add data validation rule
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 ); }
Append rows to a spreadsheet
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' ]); }
Add a line chart
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 ()); }
Clear spreadsheet content while preserving any formatting
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 (); }
Clear spreadsheet formatting while preserving any data
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 (); }
Copy data to cell range
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 6 th column . const rangeToCopy = sheet . getRange ( 1 , 1 , sheet . getMaxRows (), 5 ); rangeToCopy . copyTo ( sheet . getRange ( 1 , 6 )); }
Copy formatting to cell range
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 ); }
Get the last cell on a spreadsheet in which data is present
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 ()); }
Insert image in a spreadsheet
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 ); }
Make a copy of a spreadsheet
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 ()); }
Log the data of a spreadsheet
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 ( ',' )); } }
Retrieve a named range from a spreadsheet
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 ()); } }
Set cell formula
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)' ); }
Set cell number format
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' ); }
Set a range's values
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 ); }
Sort a range of values by multiple columns
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 }]); }
Sort a spreadsheet by a specified column
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 ); }
Update data validation rules
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 ); }
