google-apps-script

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

How to trigger a Google Apps Script once an email get in the inbox?

by

After some research and some help from other google-apps-script developers, the best solution is to use a combination of Gmail filtering system in addition to a time-driven-trigger. So basically for a normal Gmail account there is a 1 hour/day computing time as mentioned in the documentation. So what I did is set up a filter … Read more

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

How do I add formulas to Google Sheets using Google Apps Script?

by

This is done using the setFormula for a selected cell. Below is an example of how to do this. var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var cell = sheet.getRange(“B5”); cell.setFormula(“=SUM(B3:B4)”); You can also use setFormulaR1C1 to create R1C1 notation formulas. Example below. var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var cell = … Read more

How to define global variable in Google Apps Script

by

You might be better off using the Properties Service as you can use these as a kind of persistent global variable. click ‘file > project properties > project properties’ to set a key value, or you can use PropertiesService.getScriptProperties().setProperty(‘mykey’, ‘myvalue’); The data can be retrieved with var myvalue = PropertiesService.getScriptProperties().getProperty(‘mykey’);

Is it possible to ‘prefill’ a google form using data from a google spreadsheet?

by

You can create a pre-filled form URL from within the Form Editor, as described in the documentation for Drive Forms. You’ll end up with a URL like this, for example: https://docs.google.com/forms/d/–form-id–/viewform?entry.726721210=Mike+Jones&entry.787184751=1975-05-09&entry.1381372492&entry.960923899 buildUrls() In this example, question 1, “Name”, has an ID of 726721210, while question 2, “Birthday” is 787184751. Questions 3 and 4 are blank. … Read more

Determining the last row in a single column

by

How about using a JavaScript trick? var Avals = ss.getRange(“A1:A”).getValues(); var Alast = Avals.filter(String).length; I borrowed this idea from this answer. The Array.filter() method is operating on the Avals array, which contains all the cells in column A. By filtering on a native function’s constructor, we get back only non-null elements. This works for a … Read more