google-apps-script
Kalkylbladstjänst

Anmärkningar

Ark

Få en referens till en namngiven arkflik

var spread_sheet = SpreadsheetApp.getActiveSpreadsheet();//Get active spreadsheet
var sheet_with_name_a = spread_sheet.getSheetByName("sheet_tab_name");

Få aktivt arkflik

var spread_sheet = SpreadsheetApp.getActiveSpreadsheet();
var active_sheet = spread_sheet.getActiveSheet();

Sätt in kolumn

var spread_sheet = SpreadsheetApp.getActiveSpreadsheet();
var active_sheet = spread_sheet.getActiveSheet();  
active_sheet.insertColumnAfter(1);  // This inserts a column after the first column position
active_sheet.insertColumnBefore(1);  // This inserts a column in the first column position
active_sheet.insertColumns(1);  // Shifts all columns by one
active_sheet.insertColumns(1, 3);  // Shifts all columns by three
active_sheet.insertColumnsAfter(1);  // This inserts a column in the second column position
active_sheet.insertColumnsBefore(1, 5);  // This inserts five columns before the first column

Sätt in raden

var spread_sheet = SpreadsheetApp.getActiveSpreadsheet();
var active_sheet = spread_sheet.getActiveSheet();  
active_sheet.insertRowAfter(1);  // This inserts a row after the first row position
active_sheet.insertRowBefore(1);  // This inserts a row in the first row position
active_sheet.insertRows(1);  // Shifts all rows by one
active_sheet.insertRows(1, 3);  // Shifts all rows by three
active_sheet.insertRowsAfter(1);  // This inserts a row in the second row position
active_sheet.insertRowsBefore(1, 5);  // This inserts five rows before the first row

Cellvärde

var spread_sheet = SpreadsheetApp.getActiveSpreadsheet();
var active_sheet = spread_sheet.getActiveSheet();
var cell = range.getCell(1, 1);
var cell_value = cell.getValue();
cell.setValue(100);

Kopiera celler

var spread_sheet = SpreadsheetApp.getActiveSpreadsheet();
var active_sheet = spread_sheet.getActiveSheet();
var rangeToCopy = active_sheet.getRange(1, 1, sheet.getMaxRows(), 5);
rangeToCopy.copyTo(sheet.getRange(1, 6));

Formel

var spread_sheet = SpreadsheetApp.getActiveSpreadsheet();
var active_sheet = spread_sheet.getActiveSheet();
var range = active_sheet.getRange("B5");
var formula = range.getFormula()
range.setFormula("=SUM(B3:B4)");

Kopiera ett värde från ett ark till det aktuella arket

Föreställ dig att vi har ett separat Google-kalkylblad, och vi måste få B2-cellvärdet till cell D5 på ditt nuvarande ark.

function copyValueandPaste()
{  
    var source = SpreadsheetApp.openById('spread sheet id is here'); //Separate spreadsheet book
    var sourcesheet = source.getSheetByName('Sheet1'); //Sheet tab with source data
    var sourceCellValue = sourcesheet.getRange('B2').getValue(); // get B2 cell value
    
    var thisBook = SpreadsheetApp.getActive(); // Active spreadsheet book
    var thisSheet = thisBook.getSheetByName('Sheet1'); // Target sheet
    thisSheet.getRange('D5').setValue(sourceCellValue); //Set value to target sheet D5 cell
}

Du kan hitta kalkylark-ID från din URL.

Få den sista raden i en enda kolumn

function lastRowForColumn(sheet, column){
  // Get the last row with data for the whole sheet.
  var numRows = sheet.getLastRow();
  
  // Get all data for the given column
  var data = sheet.getRange(1, column, numRows).getValues();
  
  // Iterate backwards and find first non empty cell
  for(var i = data.length - 1 ; i >= 0 ; i--){
    if (data[i][0] != null && data[i][0] != ""){
      return i + 1;
    }
  }
}

Infoga matriser som rader

Att lägga in en rad i botten av ett kalkylblad är enkelt:

var someSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
someSheet.appendRow(["Frodo", "Baggins", "Hobbit", "The Shire", 33]);

Observera att detta lägger till raden efter den sista icke-tomma raden.

Att lägga in en rad någonstans i mitten är lite mer arbete:

var someSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

var newRowIndex = 2;
var row = ["Gandalf", "?", "Wizard", "?", 2019];
someSheet.insertRowBefore(newRowIndex);
// getRange(row, col, numRows, numCols)
someSheet.getRange(newRowIndex, 1, 1, row.length).setValues([row]); // Note 2D array!

Många av den här värdelösa koden kan abstraheras till en hjälpfunktion:

function insertRowBefore(sheet, rowIndex, rowData) {
  sheet.insertRowBefore(rowIndex);
  sheet.getRange(rowIndex, 1, 1, rowData.length).setValues([rowData]);
}

Som reducerar vårt exempel till bara:

var someSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
insertRowBefore(someSheet, 2, ["Gandalf", "?", "Wizard", "?", 2019]);