Google Spreadsheet и Excel полезни функции

Функции

HLOOKUP и VLOOKUP търсят задължително в първия ред/колона

=INDEX(F2:F,MATCH(1,(K2=D2:D)*(I2=B2:B)*(J2=C2:C)*(L2=E2:E),0))
Намира точната стойност на клетка, която се намира между F2:F и отговаря на определени условия

=FILTER(A2:F,IF(ISBLANK(K2),1,D2:D=K2)*IF(ISBLANK(I2),1,B2:B=I2)*IF(ISBLANK(J2),1,C2:C=J2)*IF(ISBLANK(L2),1,E2:E=L2))
Изважда целия ред намиращ се между A2:F. Когато се изтрие съдържанието на клетките, филтъра работи. Затова се ползват ISBLANK и IF. Тук съм питал дали може да се направи по-елегантно

=QUERY(A2:F, "SELECT * WHERE (B="&I2&" AND E='"&L2&"')",0)
Извежда всички редове между А2:F, за които B=I2 и E=L2


=query('Form Responses 2'!A1:B, "select * order by A desc", 1)

Сортира отговорите от Google Forms по дата (или друга колона)

=QUERY(A3:C," select * where A = "&G3&" AND B ="&H3&" " )
Изважда редовете, в които стойността в колона А=G3 И колона В=Н3
17.11.2019

=SORT('Отговори от формуляр 3'!A2:G;1;FALSE)
Сортира отговорите от 'Отговори от формуляр 3' получени чрез Google forms, между колони А и G, според  А

=ArrayFormula(IF(B2:B,$B$2:$B/$C$2:$C,))
Изпълнява формулата след първата запетая, докато има записи в B2:B

=arrayformula('Отговори от формуляр 3'!A1:G1)
Копира в нов sheet полетата между A1 и G1 на 'Отговори от формуляр 3'

=IMPORTXML("http://coinmarketcap.com/currencies/bitcoin/","//span[@class='text-large2']")
Взима стойността на class=text-large2

=RIGHT/или LEFT/ (адреса на клетката ; /или запетая/ *броя на символите, които искаш да вземеш*)

=А1&A2 - комбинира стойностите в двете клетки


Добавени на 1 Юни 2019г.:

=and($E3>now(),NOT(ISBLANK($E3)))

 - Освен, че проверява дали датата е минала, не изпълнява формулата, ако клетката е празна

JavaScript

Копиране на форматирането за цялата таблица на Form Responses, включително и за новите отговори

function copyFormat() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  sheet.getRange(3, 1, 1, lastColumn).copyFormatToRange(sheet, 1, lastColumn, 3, lastRow);
}

В последния ред на този скрипт 5 означава, че взима форматирането от ред №5 и го прилага в цялата таблица https://webapps.stackexchange.com/questions/91027/how-can-i-retain-cell-formatting-when-data-is-inputted-via-a-form

function onEdit(e) { // Премества ред от една таблица в друга, когато е написано "ГОТОВО" // see https://productforums.google.com/d/topic/docs/ehoCZjFPBao/discussion var sheetNameToWatch = "ЗА ВЗИМАНЕ"; var columnNumberToWatch = 4; // column A = 1, B = 2, etc. var valueToWatch = "ГОТОВО"; var sheetNameToMoveTheRowTo = "ВЗЕТИ"; var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = SpreadsheetApp.getActiveSheet(); var range = sheet.getActiveCell(); if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) { var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo); var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange); sheet.deleteRow(range.getRow()); } }

var range2 = e.range; range2.setNote('Последна промяна е направена: ' + new Date()); // Слага коментар с датата, на която е направена промяна

function onEdit(e) { var colToWatch = 7, colToStamp = 13; if (e.range.columnStart !== colToWatch) return; var writeVal = e.value ? new Date().new time() : ''; e.source.getActiveSheet() .getRange(e.range.rowStart, colToStamp) .setValue(writeVal);} //слага дата в колона 13 при промяна в колона 7