Hot answers tagged

4

You can create a trigger that runs every time your spreadsheet is opened. Go to Tools → Script editor and paste the following: function onOpen(e) { var spreadsheet = e.source; var sheet = spreadsheet.getActiveSheet(); var lastRow = spreadsheet.getLastRow(); if (sheet.getMaxRows() == lastRow) { sheet.appendRow([""]); } lastRow = lastRow ...


4

When using query with importrange, the imported columns must be referred to as Col1, Col2, Col3, etc, according to their positions in the imported range. So, you should replace "select D where V = 1" with "select Col4 where Col22 = 1" The reason is that imported range is not considered a part of any sheet, so its column names are not like sheet ...


3

You can do this with the indirect formula: =ARRAYFORMULA(INDIRECT("'data("&A1&")'!A:A"))


2

It's not possible to achieve the desired result by using IMPORTXML alone, I think. Instead use Google Apps Script and the XML Service Service. Example: Adaptation of the code taken from the answer by Vidar S. Ramdal function parseFeedXml(url) { var result = []; var xml = UrlFetchApp.fetch(url).getContentText(); var document = ...


1

This is probably best handled by a custom function. The function I wrote takes the data range (including the header row) as an argument and outputs the new table, with Ids and its header. Usage example: =inOut(A1:C20). It also checks data for consistency: the times must begin with In and then alternate between In and Out. function inOut(arr) { var data = ...


1

function onEdit(f) { if (f.range.getColumn() == 6 && f.value == "A" || f.value == "B") { f.range.getSheet().hideRows(f.range.getRow()); } }


1

Such locking is not possible. Users can always choose to copy one cell and not the other. Alternative suggestion: combine data A1-B1 in one cell A1, separating it by a linebreak or some other character that will not appear in the data itself. Then it will be copied as a whole. When you need to use it, you can apply split(..., char(10)) to split the ...



Only top voted, non community-wiki answers of a minimum length are eligible