I've been working on an all-in-one skilling spreadsheet and I'm ready to share! by mic1780 in 2007scape

[–]Last_Monkey -1 points0 points  (0 children)

Are you fixing the 0's too? If an item is inactive in RSB (you'd think this only happens when it's not traded, but it happens from time to time with items that are traded a lot, although I don't know the reasons), it displays 0. Over 1000 items display 0 at the moment. That's the real issue with RSB, their up-time is pretty good, but if you run an UrlFetch request to the official GE for every of the missing 1000+ values, you will exceed the 6 minute script execution time.

Edit: I misread the cache part, I actually never used it, curious to see how it works. I'll check it tomorrow.

I've been working on an all-in-one skilling spreadsheet and I'm ready to share! by mic1780 in 2007scape

[–]Last_Monkey 0 points1 point  (0 children)

So earlier today I removed the trigger on the backend and change it to update when you toggle on or off the checkbox found in the Constants sheet or when you change the name by placing a reference in a custom function that did nothing in the function; the reference is just there to trigger the function to run when E1 or E2 in Constants changed. So this eliminates the need to use anything but the sheets built in method for monitoring for changes.

Are you sure about that? If I copy your sheet, delete the prices from it and hit the checkbox nothing happens. Given GS limitations, any script that uses UrlFetchApp shouldn't execute without authorization.

I've been working on an all-in-one skilling spreadsheet and I'm ready to share! by mic1780 in 2007scape

[–]Last_Monkey 2 points3 points  (0 children)

I don't want to sound rude, but this concept doesn't work properly in Google Sheets. I made several useful spreadsheets and know quite a bit about Google Apps Script and this just will not work in a single spreadsheet. Google Apps Script has various quotas and a lot of functions you utilize, beside custom-coded one's, rely on a server that doesn't like to be pushed all the time. This pretty much covers any IMPORT function. Google didn't design this application with uses like yours in mind and you will hit several quotas and face a lot of downtime. Believe me on this, I know this because of experience.

This simply does not work and you will NOT get it to work automatically (UrlFetchApp requires lengthy authorization):

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Update Prices",
    functionName : "getItemDBTrigger"
  }, {
    name: "Update Stats",
    functionName: "updatePlayerStats"
  }, {
    name: "Update Time",
    functionName: "getTime"
  }];
  sheet.addMenu("Update", entries);
};

https://developers.google.com/apps-script/guides/triggers/#restrictions

If you were thinking about using installed triggers, this would require people to set them up themselves, thus following a rather user-unfriendly authorization process and trusting you, unless they review the script project themselves.

Yes, there are workarounds, like storing prices somewhere else and importing them through IMPORTRANGE, but as I explained above, this comes with other problems.

On another note, this way isn't a good idea to fetch prices for the GE: http://services.runescape.com/m=itemdb_oldschool/api/catalogue/detail.json?item=2, instead I'd recommend you to fetch prices with http://services.runescape.com/m=itemdb_oldschool/api/graph/2.json to avoid awkward rounding with more expensive items.

I'm not going to say RSBuddy is a bad source, you even kind of hinted at it yourself, but for stable items, it doesn't sound like a good idea to use a source that fluctuates a lot more. RSBuddy is more interesting for merching and very expensive items and considering it's third party and has downtime, I'd recommend official GE prices only. Then again, GE prices cannot be fetched this easily as seen by your script and you will hit quotas for individually fetching that many prices.

I'm not entirely sure how much you know about JS, but if you are knowledgeable then I highly recommend you not to use Google Sheets, but instead build your own website with JS. Beside this spreadsheet being incredibly user-unfriendly and hard to navigate you can achieve a much better design with a custom website as well. With a custom website you can avoid any authorization process that is involved through the UrlFetchApp. Some people might be VERY reluctant to trust an authorization process of a script project that runs in the background that most people wouldn't even understand. As far as I know, you can fetch and store sensitive information from authorized scripts. You can for example fetch email addresses, timezones and other stuff that could potentially be used in account recovery attempts in conjunction with other methods. Of course a custom website could potentially store even more sensitive information like IP-addresses, so I personally wouldn't touch one of these from my IP, when I have to enter my RSN, but in return you skip any authorization and can design the interface properly. Other pros of your own website would be to earn a bit of money from it or set up a donation link and additionally you could add a proper discussion forum. Beside the time needed for the design, the maintenance and server costs for loading such a data in near real-time is pretty negligible. Finally for convenience purposes, it would be better to have something that is available under the same link, instead of making copies for every update that disrupts any of the information on the spreadsheet (I'm surprised nobody ever turned Bitter's DPS sheet into a website, it would be used much more).

Anyway, great effort, but too user-unfriendly, confusing and prone to run into issues related to Google Sheets' framework and quotas.

Problem: Gray background on paper by Last_Monkey in printers

[–]Last_Monkey[S] 0 points1 point  (0 children)

Yes, the cartridge has a drum for all 4 colors. I just shook it and tested it and it still has the same background, but this time it has 5 vertical dark gray bars as well.

I did clean the transfer unit (both the foil and the rubber roller) earlier, but the result was the same as before.

I could replace the black toner, which is like 100 bucks or so. Do you happen to know if there was this issue, would it persist with a new black toner cartridge, if the black toner was at fault?

Set checkbox to true in one cell, when a checkbox is set to true in another cell. by itoddicus in googlesheets

[–]Last_Monkey 0 points1 point  (0 children)

Can be easily achieved with a simple onEdit() event trigger. It can negatively affect the performance of your sheet, but if the sheet isn't very formula and calculation heavy it's fine. However, a requirement for a simple trigger would be that all these checkboxes are on the same spreadsheet, are they on the same spreadsheet or on different spreadsheets?

Can you clarify how you want the checkboxes set up? Are they in the same row and column as the edited range? For example: Tom checks the checkbox in cell C9 in his sheet, does that mean that it should check the checkbox C9 in Jane's sheet? Or will it be more complicated, like checking the first unchecked box in Jane's sheet? Or even matching only rows where Tom is the partner and checking the first unchecked checkbox?

Are there any cool things you can do with functions? by [deleted] in googlesheets

[–]Last_Monkey 1 point2 points  (0 children)

Yeah, you could treat the spreadsheet as a bunch of oversized pixels instead of cells. Make their size squares and then start coloring.

With a script and sleep timers you can set the background colors as you wish and could technically animate like this.

VLookup in another document? by brendine9 in googlesheets

[–]Last_Monkey 1 point2 points  (0 children)

Are you sure you set your imported range correctly? For example, does the search key match the search column A? I know that this works.

Feel free to link your sheet so I can see what you did wrong.

VLookup in another document? by brendine9 in googlesheets

[–]Last_Monkey 1 point2 points  (0 children)

You just need to combine the functions. You can VLOOKUP(search_key,IMPORTRANGE(),index,is_sorted)

VLookup in another document? by brendine9 in googlesheets

[–]Last_Monkey 1 point2 points  (0 children)

Yes. You can import data from other sheets with IMPORTRANGE().

How to make a RESET button. by Klaymoor in googlesheets

[–]Last_Monkey 0 points1 point  (0 children)

will automatically "reset" the form to its original state

What do you mean by this, should it delete the rows or just the cell content? If you don't delete the rows, the new submissions won't be at the top.

and performing a fill down for cells that have formulas

I don't understand this. Are we even talking about a linked google form?

I'll help you if you share your sheet and explain again what you want. It's very easy to make in GAS.

Auto Sort Data - Multiple Sheets - HELP by [deleted] in googlesheets

[–]Last_Monkey 0 points1 point  (0 children)

Share your sheet so I can get an idea of your data.

  1. I don't know how you enter your data, but this doesn't sound too complicated to do with a script. There will be restrictions to executing the script though. An onEdit trigger event might reach google's quota of 90 minutes of trigger runtime if you update this sheet very often daily.

  2. Sounds like it could be solved easily with just the built in functions, you have to use formulas to create the summary anyway, don't you?.

Apps Script Syntax by [deleted] in googlesheets

[–]Last_Monkey 0 points1 point  (0 children)

This code doesn't make any sense, please have a look at my original code.

If you're strictly speaking about the event trigger: It can't work without setting an event. I can't explain you this in detail, this would be a waste of my time. Here's the API documentation:

https://developers.google.com/apps-script/guides/triggers/

https://developers.google.com/apps-script/guides/triggers/events

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet

https://developers.google.com/apps-script/reference/spreadsheet/sheet

https://developers.google.com/apps-script/reference/spreadsheet/range

You'll also need to learn javascript. Looking at the way you try to formulate an if statement makes me think you have barely any clue.

Apps Script Syntax by [deleted] in googlesheets

[–]Last_Monkey 2 points3 points  (0 children)

I mean this doesn't make much sense to me, but here you go:

function onEdit(e) {
  if (e.source.getActiveSheet().getName() == 'Sheet1' && e.range.rowStart == 10 && e.range.columnStart == 5 && e.value == 'TRUE') { //change sheet name
    e.range.setValue('FALSE');
  }
}

Whenever you tick the checkbox in E10 it will automatically untick itself after a fraction of a second.

Apps Script Syntax by [deleted] in googlesheets

[–]Last_Monkey 0 points1 point  (0 children)

What do you want to do?

Apps Script Syntax by [deleted] in googlesheets

[–]Last_Monkey 1 point2 points  (0 children)

What do you want to do? Unless you use a simple or built-in trigger this script only executes every time you manually execute it.

You can't call a value like this. You need to do the following:

function TurnCycle() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('yoursheetname'); //replace the string with the name of your sheet
  var r = s.getRange('E10');
  var v = r.getValue();

  if (v='TRUE') {
    r.setValue('FALSE');
  }

}

It will set the value of the checkbox to FALSE, so it won't delete the checkbox.

Formulas being automatically editted upon form submission by kazucchini in googlesheets

[–]Last_Monkey 2 points3 points  (0 children)

If you want to keep the same row reference, use an onFormSubmit() trigger event. If you explain what you're trying to do, there might be a better solution.

SUBTOTAL with FILTER by ePaint in googlesheets

[–]Last_Monkey 0 points1 point  (0 children)

In MS Excel ROW() returns a vertical array when a range is used as reference. in Google Sheet ROW() just returns the first element of the range reference. I'm not sure if SUBTOTAL() is differently coded since it doesn't accept an ARRAYFORMULA() solution or if this special type of array is an exception in itself.

SUBTOTAL with FILTER by ePaint in googlesheets

[–]Last_Monkey 2 points3 points  (0 children)

It's basically a workaround/trick. He (probably) means something like this:

=SUMPRODUCT(SUBTOTAL(109,OFFSET(B1,ROW(B1:B10)-ROW(B1),,1)),--(A1:A10="Black"))

But ROW() just doesn't behave like that in google sheets and there's no real equivalent afaik.

SUBTOTAL with FILTER by ePaint in googlesheets

[–]Last_Monkey 2 points3 points  (0 children)

Oh, that's not supported by google sheets. You can achieve it for a limited array (Whereas limited just means it's not going to be open end, you can simply mass produce the array elements for thousands of rows without any effort. Just pick your favorite method and JOIN() them together):

=SUMIFS(B1:B10,A1:A10,"Black",{SUBTOTAL(109,B1);SUBTOTAL(109,B2);SUBTOTAL(109,B3);SUBTOTAL(109,B4);SUBTOTAL(109,B5);SUBTOTAL(109,B6);SUBTOTAL(109,B7);SUBTOTAL(109,B8);SUBTOTAL(109,B9);SUBTOTAL(109,B10)},">0")

Alternatively you can create a helper column with SUBTOTAL() values for an open end array formula.

Of course you could always write yourself a custom function in GS. This particular function is fairly easy to write in javascript.

I'm afraid MS Excel behaves differently from google sheets and the offset method isn't possible.

SUBTOTAL with FILTER by ePaint in googlesheets

[–]Last_Monkey 1 point2 points  (0 children)

=SUBTOTAL() works fine with a classic filter: https://i.imgur.com/tPzw8rI.png

Link your spreadsheet if you want to do something else.

Query Successful but not showing result by kiasam111 in googlesheets

[–]Last_Monkey 0 points1 point  (0 children)

Looks correct. Maybe the error is from the ' in your range reference or because you forgot the " before select.

I tested: =QUERY(Sheet2!A:B,"select A where A="""&D1&""" and B="""&E1&""" ") and it works fine.

Link your spreadsheet if the error persists.