If you want to use the latest API (v4), you’ll need to do the following: Generate a spreadsheets API key (see instructions below). Make your sheet publicly accessible. Use a request of the form: https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values/RANGE?key=API_KEY You’ll then get a clean JSON response back: { “range”: “Sheet1!A1:D5”, “majorDimension”: “ROWS”, “values”: [ [“Item”, “Cost”, “Stocked”, “Ship Date”], … Read more
There is a way to make it more pythonic (works with three or more letters and uses less magic numbers): def col2num(col): num = 0 for c in col: if c in string.ascii_letters: num = num * 26 + (ord(c.upper()) – ord(‘A’)) + 1 return num And as a one-liner using reduce (does not check … Read more
It’s a little tricky because of the nested IFs but here is my answer (confirmed in Google Spreadsheets): =IF(AND(A2>=0, A2<500), “Less than 500”, IF(AND(A2>=500, A2<1000), “Between 500 and 1000”, IF(AND(A2>=1000, A2<1500), “Between 1000 and 1500”, IF(AND(A2>=1500, A2<2000), “Between 1500 and 2000”, “Undefined”))))
Google Sheets In Google Sheets, if you have a date/time value in a cell (e.g. “D9”), then use =HOUR(D9)+(MINUTE(D9)/60). If the value is stored in the format 04:29, then use =INDEX(SPLIT(D9, “:”), 1) + (INDEX(SPLIT(D9, “:”), 2)/60). Google Sheets API & Google Apps Script If you want to use the Google Sheets API or Google … Read more
JSON.parse For those who are seeing this in 2011+, as pointed out by Henrique Abreu at the Google support forum, Utilities.jsonParse is/will be deprecated. As you can see from the thread, there’s a bug with this function that it does not work when your keys are numbers, ie “1234”. As suggested, you should be using … Read more
Problem Solved. The problem is with string replace function in some other function. I am using the string replace function with regular expression as input to replace function. The regular expression was incorrect due which i am getting the above error. That’s really weird, at least GAS should give some proper error.
from the documentation : Custom functions return values, but they cannot set values outside the cells they are in. In most circumstances, a custom function in cell A1 cannot modify cell A5. However, if a custom function returns a double array, the results overflow the cell containing the function and fill the cells below and … Read more
Let’s say the In Time cells are in Column A, and Out Time cells are in Column B, and you want Time Spent to be in Column C. Put this formula in cell C2 (assuming A1, B1, and C1 contain headers, not data): =ARRAYFORMULA(B2:B – A2:A) The ARRAYFORMULA function instructs the spreadsheet to iterate the … Read more
Short answer Use an absolute cell reference or a named range Explanation Instead of E1, which is a relative cell reference, use $E$1 which is an absolute cell reference. An alternative is to to assign a name to the cell E1, let say, “constant” In the first case the formula will be =A2*$E$1 In the … Read more
This method worked for me. In markdown (or in StackOverflow), you need to have two line breaks (ie an additional one) to start new line (or paragraph). same method worked for JSDoc /** * Converts the prefixed value to the specified base. * * Requires one of the following prefixes: * * ‘0b’ Base 2: … Read more