Paul's Spreadsheets - Now with extra spice!

[pulli23]

 

[hide]Over the almost 10 years I've played runescape I've made dozens of spreadsheets for personal use. Those were always written in excel with a lot of dependance on MACRO's. This meant a lot of user knowlenge was needed for using those sheets. Not many actually participated and downloaded them (sadly).

 

Now I've had some spare time, decided to learn about google docs (and their scripting language) and decided to start converting them. In this post I'll gradually add more and more sheets to cover basic aspects of runescape. The spreadsheets posted here will depend a lot on those google scripts. (No code is executed though, everything is handled server sided). Using scripts has a few big advantages over the common "importhtml" you see in other sheets:

cleaner editing. - Scripts provide a way to handle data "out of sight", also as the importing isn't hard coded you don't have to update the html queries ever. This is all done silently

Prevents limitations. - Google had the max 50 html queries limitation on spreadsheets. With these scripts at all time only 2 queries are used.

More precise. - Without scripts you are limited in handling the data from queries, and you can only hope jagex doesn't do a small update to the GE database.[/hide]

 

 

How to use?

Spreadsheets are "easy" to use. As everything is handled server side, though there are some tricks. Alls spreadsheets will be mostly self explanatory. IE in the "prayer spreadsheet" you'll see a column with the names "total time needed" - obviously you want to minimize this, and the best method would be the lowest value here.

 

If you wish to edit the spreadsheet go to file -> make a copy. (Need a google account for this).

 

Once you have a copy you can do a few things:

• Blue cells are meant to edit.
  
• white cells are the main results you can use
  
• gray cells are "place holders".
  
• green cells are "conclusions".
  
• Prices will update everytime you open the sheet.
  
• One can force a manual update by looking into the menu "update" (next to help). Or by selecting the "update" cell/button.

 

Many efficiency sheets use a "efficiency factor" cell. This should be your personal number on how good you compare to "best", a factor of "1" indicates you gain the generally accepted max number of items / hour. - 0.5 would mean you only get half that number. Normally 0.9-0.95 are good numbers.

 

Spreadsheets

Prayer

This spreadsheet simply shows what "bones" (and familair) are best to use when house-altaring.

Input should be self explanatory. - As well as the output.

 

Food

This spreadsheet holds a foodlist, and helps you finding good (cheap food). The sheet has a hard coded limit of at least "60 healing" food, though if you wish, you can easily extend this file by going to the "Foodlist_Internal" sheet, and put extra food resources at the bottom.

Input is mostly self explanatory, constitution level is the level, Minimum healing & minimum healing are filters to filter the data.

UpdatingIs done by going to the update menu. Or by selecting the "update" cell dropdownbox.

 

 

Errors

Google spreadsheets (and especially app scripts) are still in beta, quite a few errors might happen. I'll handle a few here:

Prices don't update This is hard to pinpoint, but probably comes from a lag on the GE database. Not much I can do, other than advising you to click the button again to update prices.

Cells show "#thinking" constantly #thinking is normal, it happens when you open a spreadsheet, or make a copy. However Sometimes lag prevents the thinking to end. If it persist more than 10 seconds simply press F5.

Cells show "#name" This is a proble when creating copies: sometimes it doesn't copy the scripts. You'll have to go back to the original and recopy the sheet.

 

Closing notes

I'd really like to know what you suggest/think about these sheets. And if you like you may suggest a spreadsheet you'll like to see added.

on request, below is the code for the app scripts - I will post this in a seperate topic later maybe, so others can use it (once I find the time to write an explanation):

[hide]

function myButFunc() {
 var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("prayer");

 var t1 = 8;
 var i = t1;
 while (s.getRange(i,1).getValue() != "" ) {
   i += 1;
 }
 if ( i == t1 ) return;
 var r = s.getRange(t1,1,i-t1,1);

 var s2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("tmp");
 GetGEPrice(r, r.offset(0,4), s2);

 var t1 = 8;
 var i = t1;
 while (s.getRange(i,11).getValue() != "" ) {
   i += 1;
 }
 if ( i == t1 ) return;
 var r = s.getRange(t1,11,i-t1,1);
 GetGEPrice(r, r.offset(0,1), s2);
}

function GetGEPrice(InRange, PriceRange, temp_sheet) {

 var temprange = temp_sheet.getRange("a1")
 var items = InRange.getValues();
 var item_num = items.length;




 var str = "";
 var ItemsperPage = 20;
 var maxSearch = 10;
 var baseUrl = "http://services.runescape.com/m=itemdb_rs/results.ws?query=";


 var item_iter = 0;
 var item_string = "";


 do {

   var n = 0;
   while (item_iter < item_num && n < 10) {
     item_string += "+%22" + (items[item_iter][0]).replace(/\+/g,"*")+"%22";
     n += 1;
     item_iter += 1;
   }

   var top = baseUrl + item_string;


   temp_sheet.getRange("g1").setFormula("=ImportData(\""+top+"\")");
   temp_sheet.getRange("h1").setValue("<div id=\"search_results_text\">");
   temp_sheet.getRange("h2").setFormula("=match(h1,G:G,0)");
   SpreadsheetApp.flush();
   if (!temp_sheet.getRange("h2").getValue()) {
     alert("not all data could be loaded");
     break;
   }
   var tstr = temp_sheet.getRange(temp_sheet.getRange("h2").getValue() + 1,7).getValue();
   var page_num = Number(tstr.match(/^\s*\d+\s/))/ItemsperPage+1;


   var found_item = "";
   var ind = 0;
   var found = false;
   for (var cur_num = 1; cur_num <= page_num; cur_num += 1) {
     str = baseUrl + item_string + "&page=" + cur_num;

     temprange.setFormula("=ImportHtml(\""+str+"\",\"table\",2)");
     SpreadsheetApp.flush();
     for (var i = 0; i < ItemsperPage; i += 1) {
       found_item = temp_sheet.getRange(2 + i, 2).getValue();
       if (found_item == "") break;
       found = false;
       for (var j = 0; !found && j < item_num; j += 1) {
         tstr = items[j][0];
         if (tstr == found_item) {
           found = true;
           PriceRange.offset(j,0,1,1).setValue(UnformatNumber(temp_sheet.getRange(2 + i, 3).getValue()));
         }
       }
     }
   }
   temp_sheet.clear();
 } while (item_iter < item_num); 
}


function UnformatNumber(str) {

 if (typeof str == "number") return str;

 str = str.replace(/,/g,"");
 var re = /^(\s*([-+]?)(\d*\.?\d+)\s*([bmk]?))(.*)$/i;

 var result = "";
 var n = 0;
 var t = 0;
 var s = 1;


 while (result = re[Caution: Executable File]c(str), result) {
   if (result[2] == "-") {
     s *= -1;
   }

   t = s*Number(result[3]);
   switch (result[4]) {
   case "B": case "b":
     t *= 1000000000;
   break;
   case "M": case "m":
     t *= 1000000;
   break;
   case "K": case "k":
     t *= 1000;
   break;
   default:
     if (result[3].length != str.length) {
       t = 0;
     }
   break;
   }
   n+=t;
   str = result[5];
 }
 return n;
}

function onOpen() {
 myButFunc();
} 

[/hide]

 

 

 

Thanks for reading, and happy gaining levels,

Paul Weijtens

[Jacks0n]

looks alot like grimy's....

[Quyneax]

I like the total time calculator, nice job :). Right now the sheet doesn't list burying/ectofungus but those methods are so marginal I don't really care, though for completion's sake you should compare to ectoing infernal ashes, burying bones etcetera.

 

I think you missed one type of ourg bone, Graardor's drop. Also you have 12 for terrorbird and 18 for tortoise, which I assume are capacities, but only 25 for yak - that should be 26.

 

Just a small note layout-wise: A row of placeholders on the far left is nice especially on wider screens, and one at the top regardless. Makes it just a little easier to read.

[Grimy_Bunyip]

looks alot like grimy's....

Mine don't run off custom macros, because custom macros are a security risk compared to google doc's built in functions.

I wouldn't use a spreadsheet if i had to scan double check all your macros for key loggers every time I had to open it =\.

Of course that only applies for other people using your spreadsheet. Not you using your own spreadsheet.

 

Why don't you publish the code for the macro instead. That'd be much less of a security risk than posting a spreadsheet with some mysterious script buried in it.

 

There are ways to bypass the 50 data import limit without use of custom macros.

But, I think even my most comprehensive spreadsheet only uses 48 data imports at this moment in time.

 

Your spreadsheet does have the advantage of being able to force an update whenever you want at the click of a button.

Whereas if you use Google's built in functions, forcing an update is still possible, but is kinda tedious and annoying to explain how to do it.

[pulli23]

looks alot like grimy's....

Might be, I know there's a huge mountain I've got to live up to nowadays.

 

In the past I've decided to not upload them (yet) as there were excellent alternatives. However as those alternatives aren't there anymore I'm going to upload my own data now :). There are however a few changes:

I'm approaching xp-rates from a statistical approach. This allows people to easily add an "efficiency factor". And on top of that it is more natural, when gaming you're interested in the items/hour. Not in the exact ticks. These sheets can take "inefficciency" into account.

Also by using scripts instead of hard-coded tables to look up the GE data it is much more easy to expand might there be a big update (to either the GE or a training option), as well as a few extra features. (Think about automatic sorting, level-based methods).

 

Thanks for checking though :)

 

 

@grimy_bunyip:

Macros in a cloud-based environment, are ALL executed server sided. So there's no data execution at your PC ever, and hence no security risk at all. (Heck I can't even show an alert box if something went wrong). The worst thing a macro can do is screw it's own sheet (like putting weird data in the cells). But if you don't trust that, well sorry not much I can do then.

The code is already "there" if you copy the sheet, you can look into the code.

[Grimy_Bunyip]

In the past I've decided to not upload them (yet) as there were excellent alternatives. However as those alternatives aren't there anymore I'm going to upload my own data now :). There are however a few changes:

I'm approaching xp-rates from a statistical approach. This allows people to easily add an "efficiency factor". And on top of that it is more natural, when gaming you're interested in the items/hour. Not in the exact ticks. These sheets can take "inefficciency" into account.

Also by using scripts instead of hard-coded tables to look up the GE data it is much more easy to expand might there be a big update (to either the GE or a training option), as well as a few extra features. (Think about automatic sorting, level-based methods).

 

Thanks for checking though :)

I'm not sure what "automatic sorting, level based methods" mean.

Can you elaborate? What is it exactly that you plan to do with your scripts that you can't do with the built in functions?

Also, it's not like I stop anybody from typing item/hour directly into my spreadsheets.

They're coded to still work if you do that instead of tinkering with the game ticks.

 

@grimy_bunyip:

Macros in a cloud-based environment, are ALL executed server sided. So there's no data execution at your PC ever, and hence no security risk at all. (Heck I can't even show an alert box if something went wrong). The worst thing a macro can do is screw it's own sheet (like putting weird data in the cells). But if you don't trust that, well sorry not much I can do then.

I didn't know that, I guess that does make things like keyloggers impossible.

[pulli23]

In the past I've decided to not upload them (yet) as there were excellent alternatives. However as those alternatives aren't there anymore I'm going to upload my own data now :). There are however a few changes:

I'm approaching xp-rates from a statistical approach. This allows people to easily add an "efficiency factor". And on top of that it is more natural, when gaming you're interested in the items/hour. Not in the exact ticks. These sheets can take "inefficciency" into account.

Also by using scripts instead of hard-coded tables to look up the GE data it is much more easy to expand might there be a big update (to either the GE or a training option), as well as a few extra features. (Think about automatic sorting, level-based methods).

 

Thanks for checking though :)

I'm not sure what "automatic sorting, level based methods" mean.

Can you elaborate? What is it exactly that you plan to do with your scripts that you can't do with the built in functions?

Also, it's not like I stop anybody from typing item/hour directly into my spreadsheets.

They're coded to still work if you do that instead of tinkering with the game ticks.

 

Well with automatic sorting/level based methods I mean the "final advice". Those "green cells" currently show the total best method. However (in the uploaded sheets), what for people lacking a pak yak? When limiting for a turtoise the "best" option differs again. Obviously in this simple case it can be created by a long nested function. But I just dislike those :P, and like working from the generic case.

 

@quyneax: Updated the graphics + bug (you were correct in your assumption).

The ourg bones is weird, it's the first time I saw 2 items with the same name. I'm going to work a bit over "how would a user define wether he means the ourg bone from graardor & the ourg bones from coffins". Currently the way the ge-checker works it assumes the names of items are unique.

[JohnTheCop]

Currently the way the ge-checker works it assumes the names of items are unique.

 

You could try using the item id #'s instead if that would work, im not sure if it would as im not experienced with this sort of thing.

 

also best of luck!

[Guest jrhairychest]

Macros in a cloud-based environment, are ALL executed server sided. So there's no data execution at your PC ever, and hence no security risk at all. (Heck I can't even show an alert box if something went wrong). The worst thing a macro can do is screw it's own sheet (like putting weird data in the cells).

 

Spot-on :thumbup:

[Quyneax]

@quyneax: Updated the graphics + bug (you were correct in your assumption).

The ourg bones is weird, it's the first time I saw 2 items with the same name. I'm going to work a bit over "how would a user define wether he means the ourg bone from graardor & the ourg bones from coffins". Currently the way the ge-checker works it assumes the names of items are unique.

According to the Tip.It times trivia you can equip a dwarven army axe instead of using a tinderbox, which means your yak could hold 27 bones without having to fill it twice, and your inventory another 25. That increases inventory by 1 for all other familiars as well. Wish I'd known this when I got 99 <_< .

[jonathanfox5]

I really like the format of your sheets and I find yours more useful than grimy's (grimy's are still awesome though)

 

Nice work!

[pulli23]

Updated with a new spreadsheet. Foodlist

This sheet is simply a list of food so one can find cheap food for training/whatever you wish.

 

Sheet took a bit longer to update but that was since I've revamped the backend code a lot. Most important I've created a system where, when rs lags, it will keep trying to update the prices and not crash (after 5 seconds of lag it will simply stop wit the list). Furthermore worked a bit on the interface, for those who create a copy you should see now a shiny new menu "Update". This menu should be used to update.

The problem with the "button" is that it won't work in sheets with frozen header rows (silly google spreadsheets). So I had to chose between no frozen headers, or use a menu for updating. Hope you like my choice :).

 

 

@Quyneax, very interesting, that's something new for me too! Though can you (or anyone) confirm this is feasonable? Wouldn't it mean you'll have to drop a bone before lighting?

[Singh]

Soo when can we start editing the prayer spreadsheet for our uses?

 

 

PS the "FOOD" link goes to the prayer sheet, might wanna fix that in the first post