This is very similar to a question asked just two days ago, which was about synchronizing a spreadsheet of events with a calendar. It sounds like you want to consider the spreadsheet to be the master of events that it originates, which would simplify the problem considerably. The basics of what you need to do … Read more
You can programmatically import data from a csv file in your Drive into an existing Google Sheet using Google Apps Script, replacing/appending data as needed. Below is some sample code. It assumes that: a) you have a designated folder in your Drive where the CSV file is saved/uploaded to; b) the CSV file is named … Read more
No, there’s no equivalent to Excel’s EVALUATE() in Google Sheets. There’s long history behind this one, see this old post for instance. If you’re just interested in simple math (as shown in your question), that can be done easily with a custom function. function doMath( formula ) { // Strip leading “=” if there if … Read more
Short Answer Publish your script as an editor add-on or as a Google Workspace add-on. Bear in mind that you could make it private, by selecting unlisted or making it available only for your G Suite / Google Workspace organization. Explanation Add-ons were added on 2014. This is better than using a library because there … Read more
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
Getting spurious onFormSubmit Triggers As @J.G. pointed out I was getting more that one trigger from each submission of the form. I noticed by logging the e.values into a spreadsheet that I was not getting any of the answers. So to eliminate these unwanted triggers I just used the following logic. if(e.values && !e.values[1]){return;} where … Read more
How about this answer? Please think of this as just one of several possible answers. Issue and workaround: I could confirm about the same situation of your issue (this was reported on Google’s Issue Tracker). In this case, I think that when V8 is enabled, the form object might not be able to be parsed … Read more
I wrote a simple general custom function, which is 100% reusable you can unpivot / reverse pivot a table of any size. In your case you could use it like this: =unpivot(A1:D4,1,1,”customer”,”sales”) So you can use it just like any built-in array function in spreadsheet. Please see here 2 examples: https://docs.google.com/spreadsheets/d/12TBoX2UI_Yu2MA2ZN3p9f-cZsySE4et1slwpgjZbSzw/edit#gid=422214765 The following is the … Read more
Ok, it seems like my problem was that google behaves in a weird way – it doesn’t re-run the script as long as the script parameters are similar, it uses cached results from the previous runs. Hence it doesn’t re-connect to the API and doesn’t re-fetch the price, it simply returns the previous script result … Read more
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