google-apps-script,google-spreadsheet
You can use Spreadsheet.getRange(). For example, this should work: var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("..."); var currencyRange = ss.getRange("C29:Z250"); Well, actually I'm using this Sheet.getRange() method instead, but they should be the same in your case. It offers more ways to declare the desired range, such as getRange(row, column, numRows, numColumns), which...
javascript,google-apps-script,google-spreadsheet
You need to check if the str is present: if (str) { if (str.indexOf('task') > -1) { // Present } } Alternatively, you can use test and regex: /task/.test("task is completed"); /task/.test(str); /task/: Regex to match the 'task' test: Test the string against regex and return boolean ...
excel,google-spreadsheet,excel-formula,formula,countif
You would have to use wildcards.. please try: =countif('Responses'!B:B,"*"&A2&"*") and see if that works ?...
rss,google-spreadsheet,google-docs,drive,advanced-search
See if this works: =query('GitHub-Changelog'!A:F; "where A contains '"&C1&"' ") where C1 (on the same sheet as the formula) is the cell that holds the date (ex: Fri Jun 12)....
google-apps-script,google-spreadsheet
Use the debugger, to step through your code one line at a time. In order to do that, you'll need to make some changes to your code. Hard code some values, just for testing: function onEdit(e) { var editedCol = 24; //e.range.getColumn() var editedValue = 99; //e.range.getValue() var editedRow =...
php,google-app-engine,google-apps-script,google-spreadsheet,google-docs
In PHP, you can make an HTTP Request. In Apps Script, you can create a Stand Alone Apps Script, that does nothing but take a GET or POST request, do whatever you want it to do (E.g. write data to spreadsheet), and then return something. So, you could use an...
You can use the Google Sheets API. But you are probably better off using Apps Script to access Calendar and Sheets.
javascript,google-apps-script,google-spreadsheet
I believe I was able to solve this in a simulation environment. First of all, the onEdit method apparently doesn't work when you access other SpreadSheets as it is supposed to be for simple scripts only. So you'll need to create a function with another name and run it as...
excel,google-spreadsheet,spreadsheet
Adding a criteria for column E should be sufficient to cancel out the non-matching rows. In B6 use, =SUMPRODUCT(($E$6:$E$13=E6)*($F$6:$F$13)*($G$6:$G$13)) Fill down as necessary....
google-apps-script,google-spreadsheet
There is no programmatic access to conditional formatting in either Google Apps Script or the Spreadsheet API. All you can do is get the current format (in Apps Script only). Issue 162: Ability to set conditional formatting in SpreadsheetApp That request has been active since 2010. I don't see one...
javascript,jquery,html,google-spreadsheet,google-spreadsheet-api
There is a jquery plugin called sheetrock that does this very well, I've used it for a pretty big project myself and while it can behave strangely if you have a lot of columns in a sheet it still overall performs well: https://plugins.jquery.com/sheetrock/
google-spreadsheet,gs-conditional-formatting
$ is to "anchor" to that row/col. If you want the column to stay static, use $A2 and the row can adjust freely.
google-apps-script,google-spreadsheet,google-calendar
Serge has it right - the problem is that you've retrieved a CalendarEventSeries object, not a CalendarEvent. Since the only method in the service that will look for an event by ID is getEventSeriesById(iCalId), you're kinda stuck. One option is to use the Advanced Calendar Service instead: var event =...
google-apps-script,google-spreadsheet
That's an absolute cell reference. (The sheet's key is expected to be read from cell O2.) This range syntax is supported by Excel and Google Sheets....
google-apps-script,google-spreadsheet
This line: sinceLastMod=sinceLastMod+sinceLastMod+lastModH+" hours, " Should be: sinceLastMod=sinceLastMod+lastModH+" hours, " Remove one of the sinceLastMod variables. You've got it in there twice....
I've found a kinky solution for this. first I've divided the problem in some smaller portions. I've created an extra column what I call the 'filter' column. Next to this filter column i've matrix with all posible filter options In this spreadsheet you can see an example of this solution...
php,google-drive-sdk,google-spreadsheet,google-oauth
If you want to give the end user access to your drive, you have to give your application authority to make API calls on behalf of a user (in this case you) in your domain. For this you have to set up a service account and generate a p12 key...
You could try this (add your reference cell with date or a date to the place I've pointed with **) =ROUNDUP(MONTH(*reference or date*)/3;0) ...
Split() does not work in Arrayformula. There are workarounds. One involves using regexextract, and looks like this =ArrayFormula(IFERROR(REGEXEXTRACT(";"&B16:B,"^"&REPT(";+[^;]+",COLUMN(OFFSET(A1,,,1,3))-1)&";+([^;]+)"))) The ";" is the delimiter, the "3" would be the maximum splits/column outputs you would have in one cell. E.g: a cell with value; value; value --> would have a 3 column...
java,google-api,google-spreadsheet,gmail-api
I finally with help from here managed to create such connection. Everything is working as before. Steps You have to do is: Register at https://console.developers.google.com Create new project Under APIs & Auth -> Credential -> Create New Client ID for Service Account When the Client ID is generated You have...
Thanks @Akshin Jalilov. So assume: Column A is the dates, Column B in another holiday spreedsheet is the holidays At Column C, I check if it is public holiday or Sunday: =COUNTIF(INDIRECT("holiday!$B$2:$B$14"),"="&A2)+COUNTIF(WEEKDAY($A2),"=1") If it is holiday or sunday, then the field value is >= 1 Then I add conditional formatting...
javascript,google-apps-script,google-spreadsheet,google-docs
This appears to be Issue 3206, "Sending mail with attachment of Drawing (as PDF) consistently fails". Visit that defect and star it for updates. There doesn't appear to be a new scenario in your script, but if you have any additional info that might help the Googler who is responsible...
google-apps-script,google-spreadsheet
You need to change your if statement. It should be "===" instead of "==". Here is a good explanation behind this. Here is the final script that you can use function onEdit(e) { var sourceSheet = "Raw Data"; var columnNumberToWatch = 17; // column A = 1, B = 2,...
google-apps-script,google-spreadsheet
This is code taken directly from the documentation: function onEdit(e){ // Set a comment on the edited cell to indicate when it was changed. var range = e.range; range.setNote('Last modified: ' + new Date()); } It already has e.range in it. All you need to do is add and if...
arrays,excel,count,google-spreadsheet,row
The formula should work if you convert the booleans (true or false) returned by LEN(A1:E)>0 to numbers (1 or 0), as Barry already mentioned. This can be done quite easily by wrapping the output of the LEN()-function in an N-function or by preceding it with '--'. So, assuming your data...
google-apps-script,google-spreadsheet
Unfortunately, you cannot directly call your script from any external application. However, you can change it to be a web application, then invoke it via an HTTP GET or POST from almost anywhere, including an Android app. Insert new rows into Google Spreadsheet via cURL/PHP - HOW? provides an example...
email,outlook,google-spreadsheet,export
Yes, it is. You can develop an Outlook add-in, see Walkthrough: Creating Your First Application-Level Add-in for Outlook to get started. The NewMailEx event is fired once for every received item that is processed by Microsoft Outlook. The item can be one of several different item types, for example, MailItem,...
google-spreadsheet,google-docs
Without seeing your existing spreadsheet, Seedmanc, Google's "Filter views" option under the "Data" menu of your Sheet could be helpful, because it lets owners/editors create and save filters that are accessible to view-only users too. What's more, view-only users can create filter views unique to them. Once they enter a...
Assuming 6/1/2015 in F1 and 6/15/2015 in G1 please try: =array_constrain(arrayformula(row(A1:A100)+F$1-1),G$1-F$1+1,1) ...
google-spreadsheet,google-docs
Google sheets has a formula for that. =COUNTBLANK(range) ...
There's a new package for reading from Google sheets... https://github.com/jennybc/googlesheets I find that it's fantastic for this sort of work. Give it a shot... devtools::install_github("jennybc/googlesheets") # run this and it will ask for user authentication... gs_ls() gs_read(ws = "Your worksheet") ...
The best way for me to do this is create a custom function though google script and then use it as a formula. You can use the following script //Do Not Edit This function ADDTOTAL(range) { return range+",Total"; } And then in your sheet type with your range =SPLIT(ADDTOTAL(SORT('REPORT LOOKUP'!F:F)),",")...
google-apps-script,google-spreadsheet
There are two questions in here: You need the index of the master sheet. Say it's the first sheet: var allSheets = ss.getSheets(); //get all the sheet var indexOfMaster = 0; //or whichever the index is var sheetsExceptMaster = allSheets.splice(indexOfMaster,1); //splice out 1 item at this index, which would be...
google-apps-script,google-spreadsheet,google-spreadsheet-api
I guess this way cant work, based on this article, others are facing the same issue. I went with the image workaround that assigns the script to an image and gets called when you press the image. The following error message is displayed: You do not have permission to call...
google-apps-script,google-spreadsheet
.getRange and .setValue are the methods you will want to use in whatever you are trying to do. This little function will just take the cell notation as the first argument, and then the value you want to set as the second. function setCellValue(cellName, value) { var ss = SpreadsheetApp.getActiveSpreadsheet();...
You might want to use a built-in NOT() operator (see Filter Sample Usage): =sum(filter(fUsers,not(regexmatch(fLabel,"2nd")))) EDIT: Accoding to RE2 regex syntax, negative look-aheads are not supported. (?!re) before text not matching «re» NOT SUPPORTED This means, you cannot check with a regex if a sequence of characters is missing in the...
As far as I understand adding five business days to current date is same as adding a week. Try this: var time = new Date(+new Date + (1000 * 60 * 60 * 24 * 7)); time = Utilities.formatDate(time, "GMT-08:00", "MM/dd, E"); ...
javascript,oauth,google-drive-sdk,google-spreadsheet,google-oauth
The short answer is no. You'll be using the spreadsheets API (NOT the Drive API) to update the sheet.As far as Google is concerned, the "user" is your application, regardless of which human was driving the application at the time. Your application knows who the human is, and so it...
google-apps-script,google-spreadsheet
Here is how you do it. Add as many "else if" functions as you want. I formatted it in the it would be easy for you to edit function onEdit(e) { var cell = e.range; var cellColumn = cell.getColumn(); var cellSheet = cell.getSheet().getName(); var cellValue = e.value; var sheet =...
javascript,replace,google-spreadsheet
Instead of including DATA1 in the URL, append it with data coming from f.ex a textbox: function loadChart() { var txtbox = document.getElementById('textbox').value; var url = 'https://docs.google.com/spreadsheet/ccc?key=...&sheet=' + txtbox; var query = new google.visualization.Query(url); query.send(handleQueryResponse); } ...
You could use a formula written in Apps Script: function toTernary(decNumb) { var str = ''; do { str = String(decNumb % 3) + str; decNumb = decNumb / 3 | 0; } while (decNumb >= 1); return parseInt(str); } Results: Usage: Tools > Script Editor > Blank Paste the...
google-apps-script,google-spreadsheet
Run it on a time trigger that runs every minute until Google addresses the issues of not catching the on change event and/or not being able to define open-ended named ranges. Edited for running the script on open To keep the sheet from recalculating everytime it is opened whether needed...
google-apps-script,google-spreadsheet
I took a look at your spreadsheet and added some code: function forButton() { //Logger.log('it ran!'); var ss = SpreadsheetApp.getActiveSpreadsheet(); var activeSheet = ss.getActiveSheet(); var masterSheet = ss.getSheetByName('Master'); var inputValues = activeSheet.getRange(2, 1, 1, 3).getValues(); var secondInput = inputValues[0][1]; var allColumnTwoVals = masterSheet.getRange(2, 2, 6, 1).getValues(); allColumnTwoVals = allColumnTwoVals.join().split(","); Logger.log('secondInput:...
regex,string,split,google-spreadsheet,string-split
This may get a little 'ugly' but see if this works: =ArrayFormula(iferror(REGEXEXTRACT(" "&A1:A,"^"&REPT("\s+[^\s]+",COLUMN(OFFSET(A1,,,1,6))-1)&"\s+([^\s]+)"))) 6 in the offset denotes the (maximum) number of columns/groups that are outputted. as this is an arrayformula, no 'dragging down' is needed. ...
java,excel,google-spreadsheet,google-spreadsheet-api
getCell() only reads the local information about the cell, i.e. the stuff you inserted. You'll have to get the cell again from a CellFeed, then go through the entries to find you cell again to read back the online information. service.getFeed(cellFeedUrl, CellFeed.class); for(CellEntry ce : cellFeed.getEntries()) { Cell cell =...
google-apps-script,google-spreadsheet
You must define the method to get data in a .gs file and call it with google.script.run. In some Code.gs file: function getSheetData() { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var data = sheet.getDataRange().getValues(); return data; } And in the html script: function onScrapeClick(){ // Disable Button this.disabled = true; // Get...
Your existing google-sheets formula can make use of the iferror function. =sort(unique(transpose(split(join(";",iferror(A:A, ""))&join(";",iferror(B:B, "")),";"))),1,TRUE) ...
google-apps-script,google-spreadsheet
You are making a simple spelling error, setBackground in this case is without s because you are setting a single color to multiple cells (the argument is "#FFF") So the code is : function resetCheckDirectory() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var tableRange = ss.getRangeByName("NamedRange1"); tableRange.setBackground("#FFF"); }; to summarize : setBackground...
split,google-spreadsheet,string-split
There are many way to do so. One is to split on spaces and discard what is not required. Another is with a formula such as conventional in Excel (count the spaces by comparing the length of the string in full and after substituting spaces with nothing) and feed that...
excel,google-spreadsheet,excel-formula,array-formulas
In a google spreadsheet you may want to try: =ArrayFormula(iferror(SORT(ROW(A1:A),SORT(ROW(A1:A),A1:A,1),1)-MATCH(A1:A,SORT(A1:A),0)-ROW()+2)) Example sheet...
google-apps-script,google-spreadsheet
Sure, you can get a UI component for an add-on to open automatically when a document is opened. This is HORRIBLE behavior for an add-on, and it should never be published because it would interfere with other add-ons. But still, it can be done. There are some restrictions, though. The...
excel,google-spreadsheet,spreadsheet
For the first function, you would need to enter the following in cell F3: =IF(AND(C3="passed",D3="passed",E3="passed"),"passed","failed") This checks whether C3, D3 and E3 contain "passed" and if so returns "passed". For the second function, this is very similar: =IF(OR(C3="passed",D3="passed",E3="passed"),"passed","failed") This checks whether any of the three cells contain "passed", and if...
google-apps-script,google-spreadsheet
Henrique provides a good explanation of why custom functions like this don't update in Google Apps - script to summarise data not updating. It comes down to an optimization decision in Google Sheets; a custom function will only be re-evaluated if its parameters have changed. Try to set up an...
google-apps-script,google-spreadsheet
Something like this? This add-on uses the poller idea from How to poll a Google Doc from an add-on to call a server function getRecord(). That function grabs the row of data that is currently selected, and returns it to the showRecord() callback on the client (JavaScript) side, which handles...
Here is a workaround. Use INDIRECT() with ROW() =INDIRECT("'formsitesheet'!A"&ROW(A1)) Or =INDIRECT("'formsitesheet'!A"&ROW(A1)+X) Where X is your offset. Hope this helps...
excel,google-apps-script,google-drive-sdk,google-spreadsheet,google-apps
im giving you an algorithmic-level answer without code because you also have none. with apps script you can import the xls to drive as a new spreadsheet at regular intervals. then, copy its contents to a fixed spreadsheet (replacing previous content). other spreadsheets can read from that fixed sprradaheet with...
Assuming you have the example table in range A1:C, I think the expected result could be achieved with this formula: =ArrayFormula(iferror(vlookup(unique(B1:B),{B1:B,A1:C}, {2,3,4}, 0 ))) See if that helps ?...
google-spreadsheet,spreadsheet,pivot-table
I think this is possible using a formula: ={QUERY(A2:D,"select A,count(D) where A <> '' group by A pivot C"),QUERY(A2:D,"select count(D) where A <> ''group by A pivot B")} Here is an example sheet (the formula is in cell F1): https://goo.gl/T9xI1v...
google-apps-script,google-spreadsheet,google-spreadsheet-api,google-spreadsheet-addons
Can we also insert this clickable image and link it to app script from our server using Google Spreadsheet API? No. You can't even change the color of a cell. The best you can do is change a formula in a cell. Or Can we call app script from...
Your legacy file may be formatting it as a number, in which case you need to change your G31 to a number as well. Try =Int(Text(Today(),"yyyymmdd"))
google-apps-script,google-spreadsheet
Use setBackgrounds(). With an s since it's a method that applies multiple background colors to multiple cells
javascript,loops,google-apps-script,google-spreadsheet
I am not sure of what is the format of your startRow and startCol variables, nor to understand what you are trying to do but I think you should update the variable range in the while loop, because isBlank tests if the whole range is blank or not : https://developers.google.com/apps-script/reference/spreadsheet/range#isBlank%28%29
google-apps-script,google-spreadsheet,google-docs,add-on,url-shortener
EDIT: The OP pointed out in the comments that this is a custom function. Custom function run with limited authorization. A complete list of what is available is at: https://developers.google.com/apps-script/guides/sheets/functions#using_apps_script_services Below uses the REST API to get the shortened url. This will work with custom functions. You will just need...
google-spreadsheet,google-docs
Try this =CONCATENATE(TEXT(INDEX(SPLIT(SUBSTITUTE(A1,"Z",""),"T"),1),"yyyy-mm-dd")," ",TEXT(INDEX(SPLIT(SUBSTITUTE(A1,"Z",""),"T"),2),"hh:mm:ss")) Where A1 can be a cell with ISO-8601 formatted string or the string itself....
google-spreadsheet,gs-conditional-formatting
Go to Format tab in the google spreedsheet, then go to Conditional Formatting, then in the Apply to range box, put down your range, say A1:A30 the range where you want to apply your formatting. Then, in the Format cells if, choose Custom formula is, the last one in the...
I may be missing out on something, but can't this be accomplished by simply using open-ended ranges ? =sumif(B2:B, "CC/*", A2:A) ...
java,google-api,google-spreadsheet,gmail-api
Try adding access_type with the value offline to your auth request. I had a very similar error to this not 2 days ago and this fixed the problem. Also, just to confirm the clientID value should be similar to @developer.gserviceaccount.com They basically expect the email_address value from the console api...
regex,google-spreadsheet,spreadsheet
Maybe try =regexextract(D2, ":\s(.*?)<") ...
Use this instead: =CHAR(CODE(A1)+1) ...
I found a solution by Spioter at Excel vba to create every possible combination of a Range which proved immensely helpful! Modified the script to fit my needs and sheet which does not reflect the Google Sheet link in the question. Thank you Spioter! Sub sub_CrossJoin() Worksheets("Combinations").Range("A4:B2000").ClearContents Dim rg_Legend As...
In GDocs, you need to use a CONCATENATE function to append a subpattern to another pattern. Just using + won't do. Instead of just B16, use CONCATENATE("(?i)",B16) And the whole formula would look like =sum(filter(gaUsers,regexmatch(gaCategory,"new user funnel"),regexmatch(gaAction,CONCATENATE("(?i)",B16)))) ...
google-apps-script,google-spreadsheet
This looks a lot like what you're trying to do. For checking whether the value has truly changed after the edit, this gives you all the necessary information about it. Hence, you would know whether the new value is the same one or not. Implementing the "workaround" talked about in...
To produce the StoreName and uniqueCheckins output columns: =QUERY(QUERY(B:E,"select B, E, count(C) group by B, E",1),"select Col1, count(Col2) group by Col1 label count(Col2) 'uniqueCheckins'",1) However this will omit any StoreName that doesn't appear in the raw data (in your example, w4). Would this be OK?...
javascript,google-apps-script,google-spreadsheet,google-calendar
Even if I'm not A JavaScript expert, I'd find it more logical to write it like that (assigning a value directly to the EventId variable) : ... var EventId = createEvent(calendarId,title,startDt,endDt,desc); Logger.log('id after func = '+EventId); sheet.getRange(lr,EventIdHolder,1,1).setValue(EventId); } function createEvent(calendarId,title,startDt,endDt,desc) { var cal = CalendarApp.getCalendarById(calendarId); var start = new Date(startDt);...
function,google-apps-script,google-spreadsheet,spreadsheet
Answering my question, The main script discribs the way to find the sheet and the range(s) to protect / unprotect, for different users using the remove/addEditors(mail) function //main script - where to protect - who can edit function Protect_UnProtect( myrange, mysheet, mydescription, startRow, colnumber, numRows, columncount) { var ss =...
xml,google-apps-script,rss,google-spreadsheet,google-docs
getRange() function only assigns a range. You need to use getValues(). try changing title to this. var title = ss.getSheets()[0].getRange("A1:A").getValues();...
First register the Speadsheet: Test<- register_ss("Year") Second access Workingsheet: test<- edit_cells(Year, ws = "January", input = Test, header = TRUE, trim = FALSE) ...
google-apps-script,google-spreadsheet,google-form,formulas
Assuming your data starts in row 2, try: =ArrayFormula(iferror(vlookup(unique(B2:B), sort({B2:B, A2:A}, 2, 0), {1, 2}, 0 ))) Change ranges to suit. Don't forget to include the sheetname if you need that formula to appear on another sheet....
google-spreadsheet,spreadsheet,google-spreadsheet-api
I’m not quite sure what answer you are expecting but wanting to post an image (and probably length!) is why this is not a comment. Poor data layout that requires changes to help legibility or changes to facilitate further processing is, IMO, a very big issue – much more so...
excel,google-spreadsheet,openoffice-calc
Thank you @PsysicalChemist, the VLookup function is working in Calc to.
button,google-spreadsheet,spreadsheet
You can use Google Apps Script for this. If you are unfamiliar, in your particular spreadsheet, go to Tools → Script Editor and then select the Blank Project option. Then you can write functions like this to achieve what you want! function subtractSold() { var sheet = SpreadsheetApp.getActiveSheet(); var c1...
try using this = IF (EXACT(E3,E4), THEN Whatever)...
google-apps-script,google-spreadsheet
when replacing multiple keys in a document you are quite right that the keys must be unique. Personally I bracket all keys with # symbols which works for me e.g. #key1# won't be confused with #key10#
java,google-spreadsheet,google-spreadsheet-api
You can select columns if you use the Visualization Query Language Example that selects columns B and C (2nd and 3rd) of a big sheet (from this tutorial): https://docs.google.com/a/temet.ch/spreadsheets/d/1jwKyiVro8ZqPr5jRsyCN7igPJ76vOvtRy_xq3I8nKVI/gviz/tq?tqx=out:html&gid=0&tq=select%20B,C The query returns JSONP if you leave away the tqx=out:html parameter, but you won't get ListEntries. Note that the column names...
That is actually pretty simple. You right click one of the cells to do the formatting(one with regular for example) and select Conditional Formatting on the menu. Then you make sure the Range is the cell you right clicked, select "The text is exactly" in the list and type "Regular...
Just answered a similar question here. The workaround is to use INDIRECT() with ROW() =IF(INDIRECT("'Form Responses 1'!B"&Row(A16)) = "dog","1",IF(INDIRECT("'Form Responses 1'!B"&Row(A16))="Cat","2",IF(INDIRECT("'Form Responses 1'!B"&Row(A16))="Frog","3",IF(INDIRECT("'Form Responses 1'!B"&Row(A16))="Bird","4")))) Or =IF(INDIRECT("'Form Responses 1'!B"&Row(A1)+X) = "dog","1",IF(INDIRECT("'Form Responses 1'!B"&Row(A1)+X)="Cat","2",IF(INDIRECT("'Form Responses...
google-spreadsheet,week-number
I made a extra sheet in the spreadsheet you shared, called 'JP'. In cell A1 I entered this formula: =ArrayFormula(query({weeknum(Foglio2!A2:A)\Foglio2!B2:D}; "select Col2, Col1, sum(Col3) where Col2 <>'' group by Col2, Col1 label Col2 'Name', Col1 'Weeknumber', sum(Col3) 'Total'")) This formula outputs three columns: one column with the names, a second...
google-spreadsheet,spreadsheet,google-spreadsheet-api
As JPV commented, the "pushing down of formulae" by form submissions may be counteracted by using an array formula (in row 2): =ArrayFormula('Form Responses 1'!N2:N) And as Akshin commented, if you were in fact bringing over a couple of columns for graphing purposes, you can do it all in one...
google-apps-script,formatting,google-spreadsheet,rows,propagation
Create a function that will run when the form is submitted: Managing Triggers Manually - Google Documenation Code.gs function respondToFormSubmit() { //Format entire columns var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var range = sheet.getRange("M:Y"); range.setNumberFormat('dd.mm'); }; ...
Use embedded arrays. The simplest form is ={Sheet1!A!:B5;Sheet2!A1:B5}...
google-apps-script,google-spreadsheet,nslookup,dig
Find a web service that will let you use a GET or POST anonymously to query DNS info using a RESTful API, and you'll be able to use UrlFetchApp.fetch() to access it. For example, StatDNS has a simple API. Here's a custom function that will resolve a Domain Name to...
google-apps-script,google-spreadsheet
It was a bug. I have noticed that is better to delete the trigger, save the script, and re-create it when changes are made to the function that it calls. This way I never had problems, not doing caused strange behaviors.
google-apps-script,google-spreadsheet,spreadsheet
keyboard events for cells cannot be handled from apps script. you can only detect a cell change once it has beeb commited (user typed and pressed enter for example), which is not a keyboard event (its a range edit event)
Are you familiar using PivotTables? You could just create a PivotTable with ID as a Row Label and Behavior as a column label and Value. Then it would just be a matter of copying/pasting those values and using a MAX formula to get the greatest behavior count.
google-apps-script,google-spreadsheet,google-bigquery
Unfortunately you can only allow access to scripts running as 'you' if it is running as a web app. The only way to run it as a webapp is if the doGet()/doPost() function is called by the browser. Running the doGet() as a function runs it as a normal script....
google-apps-script,google-spreadsheet
You just have to delete the contents of the sheet before you set the new values. toWorksheet.clear() toRange.setValues(thisData.getValues()); ...
python,google-spreadsheet,gdata
Actually here is the answer to the problem: The use of client login (using username/password instead of oauth2) is likely the cause of the error. That protocol was deprecated 3+ years ago and was just shutdown. If you capture the HTTP response (which appears to have some HTML content), that...
You can do that only using a script. Try this: function PrevSheet(range) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheets = ss.getSheets(); var active = ss.getActiveSheet().getName(); for (i in sheets) { if (sheets[i].getName() === active) { return (sheets[i-1].getRange(range).getValues()); } } } The only problem is, you will have to push the...