google-sheets

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

How can I be getting multiple unwanted event blocks from the same onFormSubmit Trigger?

by

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

Moving google apps script to v8 file upload stopped working from sidebar

by

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

How do you create a “reverse pivot” in Google Sheets?

by

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

Convert column index into corresponding column letter

by

I wrote these a while back for various purposes (will return the double-letter column names for column numbers > 26): function columnToLetter(column) { var temp, letter=””; while (column > 0) { temp = (column – 1) % 26; letter = String.fromCharCode(temp + 65) + letter; column = (column – temp – 1) / 26; } … Read more

Why do we use SpreadsheetApp.flush();?

by

A programmer will use flush() when they want to ensure that the previous code’s output and/or effects are written to the spreadsheet before continuing. If you do not flush(), then the code may be automatically “optimized” by using some built-in caching and bundling of operations. In general, you do not need to use flush() until … Read more

Long processing time likely due to getValue and cell inserts

by

Issue: Usage of .getValue() and .setValue() in a loop resulting in increased processing time. Documentation excerpts: Minimize calls to services: Anything you can accomplish within Google Apps Script itself will be much faster than making calls that need to fetch data from Google’s servers or an external server, such as requests to Spreadsheets, Docs, Sites, … Read more

Refresh data retrieved by a custom function in Google Sheet

by

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

What does the range method getValues() return and setValues() accept?

by

Documentation excerpts: From The official documentation, getValues() returns a two-dimensional array of values, It ALWAYS returns a two dimensional array of values. One dimensional array is [1,2,3] Two dimensional array is [[1,2,3]] //or [[1], [2], [3]] There is/are array(s) inside a array. indexed by row, then by column. It is indexed by row first: i.e., … 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

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