Apri un foglio di lavoro
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;
}
Aggiungi regola di convalida dei dati
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);
}
Aggiungi righe a un foglio di lavoro
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']);
}
Aggiungi un grafico a linee
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());
}
Cancella i contenuti del foglio di lavoro ma preserva la formattazione
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();
}
Copia i dati nell'intervallo di celle
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);
}
Prendi in considerazione l'ultima cella di un foglio di lavoro in cui sono presenti dati
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());
}
Inserisci immagine in un foglio di lavoro
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);
}
Crea una copia di un foglio di lavoro
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());
}
Registra i dati di un foglio di lavoro
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(','));
}
}
Recupera un intervallo con nome da un foglio di lavoro
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');
}
Impostare i valori di un intervallo
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);
}
Ordina un intervallo di valori in base a più colonne
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}]);
}
Ordina i dati di un foglio di lavoro in base a una colonna specifica
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);
}
Aggiorna le regole di convalida dei dati
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);
}