SpreadsheetApp

Tabelle öffnen

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;
}

Datenvalidierungsregel hinzufügen

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);
}

Zeilen zu einer Tabelle hinzufügen

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']);
}

Liniendiagramm hinzufügen

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());
}

Tabelleninhalt löschen, aber Formatierung beibehalten

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();
}

Tabellenformatierung löschen, aber Daten beibehalten

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();
}

Daten in Zellbereich kopieren

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));
}

Formatierung in den Zellbereich kopieren

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);
}

Letzte Zelle mit Daten aus einer Tabelle abrufen

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());
}

Bild in Tabelle einfügen

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);
}

Kopie einer Tabelle erstellen

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());
}

Daten einer Tabelle protokollieren

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(','));
  }
}

Benannten Bereich aus einer Tabelle abrufen

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());
  }
}

Formel der Zelle festlegen

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)');
}

Zahlenformat der Zelle festlegen

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');
}

Werte eines Bereichs festlegen

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);
}

Wertebereich nach mehreren Spalten sortieren

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}]);
}

Tabelle nach einer festgelegten Spalte sortieren

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);
}

Datenvalidierungsregeln aktualisieren

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);
}