3

You can build a custom parser for any fixed JSON string using regular expressions. For example, if you want to extract the child items into columns you can do the following (assuming your JSON string is in A1): =SPLIT(REGEXREPLACE(INDEX(REGEXEXTRACT(A1,"(\[(\n|\s|""|\w|,)+\])"),1),"(\[|\]|\n|\s|"")",""),",") You can adapt this formula to suit whatever ...


2

Use batch operations instead of unit operations. In other words, make an effort to avoid or reduce reading / writing of single cells specially in loops. When it is possible read all the values that you will need on a single operation, i.e. instead of using a for loop and getValue() to read a single value on each iteration use getValues() to read all the ...


1

It sounds to me like you're trying to use a spreadsheet to do the job of a database. (2000 rows is nothing. But three concurrent users, 13 tabs, feeding three other worksheets .. put those together and you're not in spreadsheet-land any more). I'd suggest a review of your overall IT and business architecture, and make decisions from there about what ...


1

Redirectnator v1.1 is a spreadsheet created by Jeff Louella. It has a function built-in, with server code (301/302/200/404) and final url. You can make a copy here : https://www.thetechseo.com/seo-tools/redirect-checker/ Here's the script you can use/modify : function redirectCheck(url, user, pwd) { try { function getResp(url, user, pwd){ ...


1

Google Sheets user interface doesn't include a way to locate a sheet by name. At the bottom there is a tab for each sheet, but they aren't sorted alphabetically and some sheets could be hidden; also there is a button that shows the list of sheets shown and hidden but this list isn't sorted either. Google Apps Script could be used to find the sheet. I.E. ...


1

The problem is that each formula that use your custom function triggers the execution of a new instance of the related script when the spreadsheet is recalculated. The obvious way to avoid to trigger so much script execution instances is to reduce the number of formulas that use the custom function, the way to get the required number of results is to make ...


1

With some advice from a commenter and a little additional tweaking, I've gotten the script to work exactly how I wanted. I had a little extra difficulty with the fact that I was trying to synchronize two different pairs of cells, and at first I could only get one pair or the other to sync, not both, but I managed to solve that issue with this script, which ...


1

There is a misunderstanding regarding the third parameter of getRange on var fillDownRange = ss.getRange(2424, 3, lr); as this parameter is the height of the range but what you expect is that it's the last row. To fix your code replace var fillDownRange = ss.getRange(2424, 3, lr); by var fillDownRange = ss.getRange(2424, 3, lr - 2424 + 1);


1

This has ben solved. I was targeting the wrong column, solution was: sales.getRange (2, 5, salesLR).insertCheckboxes(); sales.getRange (2, 6, salesLR).insertCheckboxes();


1

I tried this and its actually worked . function onEdit(e) { var sheet = SpreadsheetApp.getActiveSheet(); var r = sheet.getRange("L2").getValue(); if (r == 'ADD'){ // Add your code here }}


1

From my answer to Move to last line of Google Document when it is opened Try this function onOpen(){ var doc = DocumentApp.getActiveDocument(); var body = doc.getBody(); var numChildren = body.getNumChildren(); var pos = doc.newPosition(body.getChild(numChildren - 1),0); doc.setCursor(pos); }


1

I had the same issue and tried to solve it with Vidar's answer but because of deprecation it didn't work. The actual solution is the link by @hadi in the comment on Vidar's answer. Vidar : This is a great answer. I cleaned it up, and updated some of the deprecated methods, and got rid of unnecessary functions, and also modified it to be run from ...


Only top voted, non community-wiki answers of a minimum length are eligible