google-sheets

Find Cell Matching Value And Return Rownumber

by

Here the code function rowOfEmployee(){ var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var data = sheet.getDataRange().getValues(); var employeeName = sheet.getRange(“C2”).getValue(); for(var i = 0; i<data.length;i++){ if(data[i][1] == employeeName){ //[1] because column B Logger.log((i+1)) return i+1; } } } When you want to perform this kind of lookup it is better to retrieve data with sheet.getDataRange().getValues() because in this … Read more

Create a new sheet in a Google Sheets with Google Apps Script

by

Surprisingly I didn’t find any clear and quick answer. So here is my code: function onOpen() { var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var yourNewSheet = activeSpreadsheet.getSheetByName(“Name of your new sheet”); if (yourNewSheet != null) { activeSpreadsheet.deleteSheet(yourNewSheet); } yourNewSheet = activeSpreadsheet.insertSheet(); yourNewSheet.setName(“Name of your new sheet”); } Finally, note that this new sheet will be automatically the … Read more

Reference a cell using formula?

by

You can use indirect() to dynamically reference cells Use: indirect(“string that evaluates to a reference”) example: =Indirect(“A”&B2) copies the value of the cell with the row in B2 in column A In your case, it would be (replace [column] with the desired column): indirect(“[column]”&ArrayFormula(MAX(FILTER(ROW(B:B);NOT(ISBLANK(B:B))))))

Script to Change Row Color when a cell changes text

by

//Sets the row color depending on the value in the “Status” column. function setRowColors() { var range = SpreadsheetApp.getActiveSheet().getDataRange(); var statusColumnOffset = getStatusColumnOffset(); for (var i = range.getRow(); i < range.getLastRow(); i++) { rowRange = range.offset(i, 0, 1); status = rowRange.offset(0, statusColumnOffset).getValue(); if (status == ‘Completed’) { rowRange.setBackgroundColor(“#99CC99”); } else if (status == ‘In Progress’) … Read more

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)