SpreadsheetApp
Stay organized with collections
Save and categorize content based on your preferences.
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
();
}
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
));
}
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
());
}
}
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'
);
}
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
);
}
