Details:
I’m having some trouble to achieve a result needed for my SlideOS project.
I’m using an API to get some data trough IMPORTJSON, because of that I’m not able to use ARRAYFORMULA to repeat the IMPORTJSON formula in every new row.
What I want to achieve:
When a user submits a new item, the IMPORTJSON formula is copied to this new row. As I said, IMPORTJSON is not supported by ARRAYFORMULA, so I think that the best option is a script.
The problem:
I know almost nothing about Google Scripts, I was able to find a script to add the formula to each row but only when I edit the first row. I tried to work around this script but without luck.
The Script:
function onEdit(e){
var activeSheet = e.source.getActiveSheet();
var tabs = ['sailor'];
var columns =[12];
if(tabs.indexOf(activeSheet.getName()) !== -1){
var cell = e.range;
var col = cell.getColumn();
if(columns.indexOf(col) !== -1){
if(cell.getFormula() !== ""){
var destination = activeSheet.getRange(2, col, activeSheet.getLastRow()-1, 1);
cell.copyTo(destination);
}
}//IF columns match }
}//IF tab name matches
}
What does the Importjson formula actually do. Is it returning more than one row? Is that the issue? I assume it’s a custom function you got somewhere. Couldn’t you just put the custom formula at the top row and populate the entire column via the custom code?
I am not familiar with the importjson function but could you “hide” functions with an If function like:
=if(a2=“”,””,IMPORTJSON())
This could be copied to the appropriate cells and whenever the cell that receives data (in this case A2) is not blank, it runs the IMPORTJSON function (or whatever function you want).
Hi @George_B, I’m using the IMPORTJSON to retrieve title and description for a URL (using an api), the result is just one 1 row and 2 columns - in fact, I’m using 2 formulas, one for each column because was the best way to work with the results.
I also tried with IMPORTXML, I can get the data but it has the same incompatibility with ARRAYFORMULA.
The script I posted was just one of my attempts to auto populate the new rows (when a user submits a url), but I can’t get anywhere.
I think that this is not possible in my case. What i need is when a user submits a new URL to the sheet, the new row is created with the url and it auto populates with a formula like:
What I did in a similar situation when using a custom function was to create a second function that updated the entire column with data. I placed it on row 2 and the function itself returned an array of data back to that cell. Each element of that array of data was obtained by a function that returned a single “row” of data that used data from other columns in that specific row. It does update the entire column any time there is a change of data on the sheet so with a large amount of data it could prove to be very slow. This worked for me since the number of rows is limited and additional rows are only added periodically.
I’m guessing that your solution would be the best to just check for an empty cell n that column and writing the custom formula back to the empty cell.
Update: In your case you will have to use a timed trigger and change the function, to point to a specific sheet and column. I would then suggest you travers the column from the last row up so you would only be updating a few cells before you encounter one that already has the formula in it.
You are right, what I suggested doesn’t work by default.
However, I gave something a test:
If you array formula your responses to another sheet or filter if not blank, this could help.
In the new sheet (let’s call it array) you pull the whole column of your form sheet (C2:C for instance). In your array sheet you can use my above method of if(C2="","",IMPORTJSON()).
Since the array formula auto-pulls any information from the forms sheet it would automatically add any new submissions.
I tested adding some form submissions for another app I had and was able to get it to work based on what I understand you’re looking for… although I admit I might not fully understand what you are trying to accomplish.
Side thought: If you need an auto-updated row number and column letter you could use an indirect function…?
Instead of typing E2 use this - Indirect(“E”&row()). I believe this would take the row number / count and place that respective number there.
Just trying to be a contributor amidst the chaos of life. I hope you can figure it out and Glide can continue to become a great platform for all to create and collaborate.
I was going to suggest the second sheet method that @RedHeadKev suggested, using a query or whatever method to pull in rows into a second sheet preloaded with formulas. I’ve done it a few times preloading 1000 or 2000 rows with formulas that won’t work with arrayformulas.
Thanks guys, I appreciate all the tips and ideas but I’m still a bit lost with your suggestions and I’m not sure if it is exactly what I’m looking for (or at least if it is the best - read most “row efficient” and faster - way to do it. I was really hoping that a script would do the job, something like: For every new row, paste formula X (with the right row/cell references) to column Y.
Let me give some more details, maybe it helps:
I’m build a “Browser” for SlideOS (my “NoCode OS” glide app). This browser app consist in a text input where users can just type a URL and open it in a webview and a Favorites list filtered per user.
Each user can add new favorites by clicking “⭑ Add to Favorites” and submitting the URL and a Title. I can automatically grab the url description using IMPORTXML or IMPORTJSON via an API, so I was wondering if I could automatically "paste"the right formula to the “description” column every time a user submits a new favorite.
The app is only a project to create some crazy stuff using Glide, so it isn’t an PRO app. I’m already using more than 170 rows from the free tier, using the leas amount of rows possible is a must for me at this point as I still want to build at least 6 other apps.
If you use the 2 sheet method, you can aquire the data from the users, duplicate it in another sheet with prefilled formulas for the imports, then pull that column back into the original sheet. This should not count against your row count because the second sheet won’t be used int the app. I do offloading in my app using a similar method, I offliad to a completely separate spreadsheet using importrange, then pull the calculated data back into the original spreadsheet when it’s done calculating. No scripts involved.
