I have a data file loading into a Google Sheet.
Following other processing, this data has only two columns, SKU (A) and Stock on Hand (B)
The problem I have is that the stock on hand contains values of >10
I want to find/replace anything that’s >10 with simple the number 10.
I’ve got as far as trying a “Search Rows” which has a filter of B = >10
This is returning bundles each value that matches, with value 1 as >10
This is where I’m stuck. How can I update each of these found rows (cells) to be simply 10, based on the search results?
(BONUS question - if the above is possible I have another sheet with negative values. Can I pattern match any value that’s negative eg -25 to replace to 0?)
This is how you would do it:
-
Search for rows more than 10
-
Filter when it finds data
3.Update row using the row number from the search:
Do the same for less than 0 on the path below:
1 Like
Thanks for your reply, very helpful.
I was actually looking for the STRING “>10” to replace with the value 10, I didn’t mean it as an operator for the first one, but I think I’ve managed to get part sorted regardless thanks to your help with a simplified version of the above. My mistake was I was trying to use Update a Cell module when I needed to be using Update a Row.
Now, I think I’ve got this bit working, and the Update a Row module runs 3 times on my test data (but would be a few hundred times on the real data).
BUT, then if I add more modules following, such as a Google Drive Download a File option, it tries to download the file once per run. How do I get this to complete ALL it’s runs for the update a row, before I get the next step to occur of downloading the file, and do that next step once only?
I thought it might be an aggregator but I didn’t seem to be able to get that to work. Would you have any more tips on this?
What I want to do in the end is to send a single XLSX file as the final output, to my FTP server.
You would need to use an array aggregator after the update a row modules. Then it will complete all the updates first.
1 Like
Thankyou.
I’ve now run into another problem.
My data file is around 8500 rows, but 3500 of them need this replacement.
Based on your help I now have the scenario working perfectly with my small test data file. However with the real data it needs to run around 3500 operations. Which is too many I’d say. Because of this, I end up with Google Sheets crashing out for too many requests after a couple of hundred.
Do you have any suggestions on how to solve this. I see there is a sleep option, but sleeping maybe 15 times is probably not the best solution.
Do you have any further suggestions, or is Make not really the best tool for something like that?
Thankyou again for your help thus far.
You can use the bulk update google sheets module.
1 Like
Thanks for your help, apologise for the continued questions, I feel I’m almost there but not quite.
I have the Array Aggregator set up like this, which shows up as the “INPUT”
This results in a data structure that looks like this in the Input Section:
Under the Output section on the array, it initially shows like this:
If I then click to expand the array, I get a series of collections with values underneath, all looking correct, like this:
HOWEVER: When I go to the next module, the Bulk Update Rows Module, in the data selector, it shows up like this, and it will NOT let me expand any further. Ie, I’m not seeing the results that are clearly showing on the one module, on the next module it just doesn’t show. Like this. Thus, anything else I do on the update rows module doesn’t work, as it’s not seeing the data properly. Literally all that’s there is [1] with 8577
The number 8577 is the number of rows in the file, and the other value 3327 is the number of rows that are to be updated. But I can’t find any other values in the input data to map to, they just aren’t showing.
Any ideas on what could be wrong?
Yes just put the entire array in the mapping. Literally just select the Array
So like this?
If so, where do I put what I want the value to change TO?
As in if I want to set the value of all the searched/selected fields in the array valve “10”, where do I put the 10?
I feel like I’m missing something obvious here. Is this module designed only to be used TOGETHER with a standard “Update Row” module or something? Am I incorrect to think that it works by itself?
yes like that. that should work for you.
It doesn’t. It gives this error: The operation failed with an error. 400: INVALID_ARGUMENT - Requested writing within range [‘testfile-temp.csv’!B2:B10000], but tried writing to column [C]
Also as per my previous message I can’t see where I add the value I WANT the data to change to?
- I can see I’m FINDING a bunch of rows based on a search criteria. That’s good.
- I’m SELECTING which column I want to update within those rows (Column B)
- WHERE IN THE WORLD DO I PUT WHAT I WANT THE VALUE TO UPDATE TO?
I can’t see where I’ve put that or where I should put that, and I feel like that’s the missing piece…
Ahh yes you need to increase the range of writing the data so from
B2:Z10000
It doesn’t matter if you put z it will just give a larger space to input.
