Size
24.0 KB
Version
1.1.27
Created
Dec 19, 2025
Updated
4 months ago
Find page numbers in Limoanywhere for zone codes from Google Sheets and add them to column H
1// ==UserScript==
2// @name Limoanywhere Page Finder for Google Sheets
3// @description Find page numbers in Limoanywhere for zone codes from Google Sheets and add them to column H
4// @version 1.1.27
5// @match https://*.docs.google.com/*
6// @match https://manage.mylimobiz.com/*
7// @icon https://ssl.gstatic.com/docs/spreadsheets/spreadsheets_2023q4.ico
8// ==/UserScript==
9(function() {
10 'use strict';
11
12 console.log('Limoanywhere Page Finder extension loaded');
13
14 // ========== GOOGLE SHEETS FUNCTIONS ==========
15
16 async function extractSheetDataManually() {
17 const instructions = `
18INSTRUCTIONS:
19
201. Select all your filtered data (columns A through G, all 562 rows)
212. Copy it (Ctrl+C or Cmd+C)
223. Click OK below
234. The extension will read from your clipboard
24
25Make sure:
26- Column B has Zone From codes
27- Column D has Rates
28- Only include rows with rates < 130
29 `;
30
31 alert(instructions);
32
33 // Try to read from clipboard
34 try {
35 const clipboardText = await navigator.clipboard.readText();
36 console.log('Clipboard data received, length:', clipboardText.length);
37
38 // Parse the clipboard data (TSV format from Google Sheets)
39 const lines = clipboardText.split('\n');
40 const data = [];
41
42 lines.forEach((line, index) => {
43 if (!line.trim()) return;
44
45 const columns = line.split('\t');
46 console.log(`Row ${index}: Found ${columns.length} columns`);
47
48 if (columns.length >= 4) {
49 const vehicleCode = columns[0].trim(); // Column A
50 const zoneFrom = columns[1].trim(); // Column B - Zone From
51 const zoneTo = columns[2].trim(); // Column C - Zone To
52 const rateText = columns[3].trim(); // Column D - Rate
53 const rate = parseFloat(rateText.replace(/[^0-9.-]/g, ''));
54
55 // Skip header row
56 if (vehicleCode === 'Vehicle Code' || zoneFrom === 'Zone From (Code)') {
57 console.log('Skipping header row');
58 return;
59 }
60
61 if (!isNaN(rate) && rate < 130 && rate > 0 && zoneFrom) {
62 data.push({
63 rowIndex: index + 2, // Approximate row number
64 zoneCode: zoneFrom,
65 zoneTo: zoneTo,
66 vehicleCode: vehicleCode,
67 rate: rate
68 });
69 console.log(`Found: Row ${index + 2}, Vehicle: ${vehicleCode}, Zone From: ${zoneFrom}, Zone To: ${zoneTo}, Rate: ${rate}`);
70 }
71 }
72 });
73
74 console.log(`Total found: ${data.length} entries with rates < 130`);
75 return data;
76
77 } catch (error) {
78 console.error('Error reading clipboard:', error);
79 alert('Could not read clipboard. Please make sure you copied the data first.');
80 return [];
81 }
82 }
83
84 async function extractSheetData() {
85 console.log('Extracting data from Google Sheets...');
86
87 const data = [];
88
89 // Try to find all divs that might contain cell data
90 const allCells = document.querySelectorAll('div[role="gridcell"]');
91 console.log(`Found ${allCells.length} cells with role="gridcell"`);
92
93 // Group cells by row
94 const rowMap = {};
95 allCells.forEach(cell => {
96 const rowIndex = cell.getAttribute('aria-rowindex');
97 const colIndex = cell.getAttribute('aria-colindex');
98
99 if (rowIndex && colIndex) {
100 if (!rowMap[rowIndex]) {
101 rowMap[rowIndex] = {};
102 }
103 rowMap[rowIndex][colIndex] = cell.textContent.trim();
104 }
105 });
106
107 console.log(`Found ${Object.keys(rowMap).length} rows`);
108
109 // Process the row map
110 Object.keys(rowMap).forEach(rowIndex => {
111 const row = rowMap[rowIndex];
112 const zoneCode = row['2']; // Column B
113 const rateText = row['4']; // Column D
114
115 if (zoneCode && rateText) {
116 const rate = parseFloat(rateText.replace(/[^0-9.-]/g, ''));
117
118 if (!isNaN(rate) && rate < 130 && rate > 0 && zoneCode) {
119 data.push({
120 rowIndex: parseInt(rowIndex),
121 zoneCode: zoneCode,
122 rate: rate
123 });
124 console.log(`Found: Row ${rowIndex}, Zone: ${zoneCode}, Rate: ${rate}`);
125 }
126 }
127 });
128
129 console.log(`Total found: ${data.length} entries with rates < 130`);
130 return data;
131 }
132
133 async function writePageNumbersToSheet(pageNumberMap) {
134 console.log('Writing page numbers to column H...');
135
136 // This function will write the page numbers to column H
137 // For each row in pageNumberMap, we need to click on the cell in column H and enter the page number
138
139 for (const [rowIndex, pageNumber] of Object.entries(pageNumberMap)) {
140 try {
141 // Find the cell in column H (aria-colindex="8") for this row
142 const cell = document.querySelector(`[role="gridcell"][aria-rowindex="${rowIndex}"][aria-colindex="8"]`);
143
144 if (cell) {
145 // Click the cell to select it
146 cell.click();
147 await new Promise(resolve => setTimeout(resolve, 100));
148
149 // Type the page number
150 const input = cell.querySelector('input') || cell;
151 if (input.tagName === 'INPUT') {
152 input.value = pageNumber;
153 input.dispatchEvent(new Event('input', { bubbles: true }));
154 } else {
155 // Use document.execCommand for contenteditable cells
156 document.execCommand('insertText', false, pageNumber.toString());
157 }
158
159 // Press Enter to confirm
160 const enterEvent = new KeyboardEvent('keydown', {
161 key: 'Enter',
162 code: 'Enter',
163 keyCode: 13,
164 bubbles: true
165 });
166 cell.dispatchEvent(enterEvent);
167
168 await new Promise(resolve => setTimeout(resolve, 100));
169 }
170 } catch (error) {
171 console.error(`Error writing to row ${rowIndex}:`, error);
172 }
173 }
174
175 console.log('Finished writing page numbers');
176 }
177
178 function createSheetsUI() {
179 // Check if button already exists
180 if (document.getElementById('limo-finder-button')) {
181 return;
182 }
183
184 const button = document.createElement('button');
185 button.id = 'limo-finder-button';
186 button.textContent = 'Find Limoanywhere Pages';
187 button.style.cssText = `
188 position: fixed;
189 top: 20px;
190 right: 20px;
191 background: #1a73e8;
192 color: white;
193 border: none;
194 border-radius: 4px;
195 padding: 10px 16px;
196 font-size: 14px;
197 font-weight: 500;
198 cursor: pointer;
199 box-shadow: 0 2px 8px rgba(0,0,0,0.15);
200 z-index: 10000;
201 font-family: 'Google Sans', Arial, sans-serif;
202 transition: background 0.2s;
203 `;
204
205 button.addEventListener('mouseenter', () => {
206 button.style.background = '#1557b0';
207 });
208
209 button.addEventListener('mouseleave', () => {
210 button.style.background = '#1a73e8';
211 });
212
213 button.addEventListener('click', async () => {
214 button.textContent = 'Extracting data...';
215 button.disabled = true;
216
217 try {
218 // First try automatic extraction
219 let sheetData = await extractSheetData();
220
221 // If no data found, try manual clipboard method
222 if (sheetData.length === 0) {
223 console.log('Automatic extraction failed, trying clipboard method...');
224 sheetData = await extractSheetDataManually();
225 }
226
227 if (sheetData.length === 0) {
228 alert('No entries found with rates < 130. Please make sure:\n1. You have scrolled through all filtered data\n2. Column B has zone codes\n3. Column D has rates\n4. Or try copying the data to clipboard first');
229 button.textContent = 'Find Limoanywhere Pages';
230 button.disabled = false;
231 return;
232 }
233
234 alert(`Found ${sheetData.length} entries with rates < 130. Opening Limoanywhere...`);
235
236 // Store the data in GM storage
237 await GM.setValue('sheetData', JSON.stringify(sheetData));
238
239 // Open Limoanywhere in a new tab
240 button.textContent = 'Opening Limoanywhere...';
241 await GM.openInTab('https://manage.mylimobiz.com/admin/manageRateZones.asp?stab=rateManagement&action=batchEdit&fixedRateMatrixId=18105', false);
242
243 button.textContent = 'Processing in Limoanywhere tab...';
244
245 } catch (error) {
246 console.error('Error:', error);
247 alert('Error: ' + error.message);
248 button.textContent = 'Find Limoanywhere Pages';
249 button.disabled = false;
250 }
251 });
252
253 document.body.appendChild(button);
254
255 // Create a second button to write results to column H
256 const writeButton = document.createElement('button');
257 writeButton.id = 'limo-write-button';
258 writeButton.textContent = 'Write Results to Column H';
259 writeButton.style.cssText = `
260 position: fixed;
261 top: 70px;
262 right: 20px;
263 background: #0f9d58;
264 color: white;
265 border: none;
266 border-radius: 4px;
267 padding: 10px 16px;
268 font-size: 14px;
269 font-weight: 500;
270 cursor: pointer;
271 box-shadow: 0 2px 8px rgba(0,0,0,0.15);
272 z-index: 10000;
273 font-family: 'Google Sans', Arial, sans-serif;
274 transition: background 0.2s;
275 `;
276
277 writeButton.addEventListener('mouseenter', () => {
278 writeButton.style.background = '#0d8043';
279 });
280
281 writeButton.addEventListener('mouseleave', () => {
282 writeButton.style.background = '#0f9d58';
283 });
284
285 writeButton.addEventListener('click', async () => {
286 writeButton.textContent = 'Preparing...';
287 writeButton.disabled = true;
288
289 try {
290 const pageNumberMapStr = await GM.getValue('pageNumberMap');
291 const sheetDataStr = await GM.getValue('sheetData');
292
293 if (!pageNumberMapStr || !sheetDataStr) {
294 alert('No results found. Please run the search in Limoanywhere first.');
295 writeButton.textContent = 'Write Results to Column H';
296 writeButton.disabled = false;
297 return;
298 }
299
300 const pageNumberMap = JSON.parse(pageNumberMapStr);
301 const sheetData = JSON.parse(sheetDataStr);
302
303 // Create a map of zone codes to page numbers
304 const zoneToPageMap = {};
305 sheetData.forEach(entry => {
306 if (pageNumberMap[entry.rowIndex]) {
307 zoneToPageMap[entry.zoneCode] = pageNumberMap[entry.rowIndex];
308 }
309 });
310
311 const foundCount = Object.keys(zoneToPageMap).length;
312
313 // Create a simple text format: Zone Code = Page Number
314 let resultsText = 'Zone Code\tPage Number\n';
315 Object.entries(zoneToPageMap).sort().forEach(([zone, page]) => {
316 resultsText += `${zone}\t${page}\n`;
317 });
318
319 // Copy to clipboard
320 await GM.setClipboard(resultsText);
321
322 alert(`Found ${foundCount} page numbers!\n\nThe results have been copied to your clipboard in this format:\nZone Code → Page Number\n\nYou can:\n1. Paste this into a new column to see the mapping\n2. Use VLOOKUP to match zone codes and fill column H\n\nFormula for H2: =VLOOKUP(B2, PasteRange, 2, FALSE)`);
323
324 writeButton.textContent = 'Write Results to Column H';
325 writeButton.disabled = false;
326
327 } catch (error) {
328 console.error('Error:', error);
329 alert('Error: ' + error.message);
330 writeButton.textContent = 'Write Results to Column H';
331 writeButton.disabled = false;
332 }
333 });
334
335 document.body.appendChild(writeButton);
336
337 console.log('Google Sheets UI created');
338 }
339
340 // ========== LIMOANYWHERE FUNCTIONS ==========
341
342 async function searchLimoanywhere() {
343 console.log('Starting Limoanywhere search...');
344
345 // Get the sheet data from storage
346 const sheetDataStr = await GM.getValue('sheetData');
347 if (!sheetDataStr) {
348 alert('No sheet data found. Please run the extension from Google Sheets first.');
349 return;
350 }
351
352 const sheetData = JSON.parse(sheetDataStr);
353 console.log(`Searching for ${sheetData.length} zone codes`);
354
355 // Get or initialize the search state
356 let searchStateStr = await GM.getValue('searchState');
357 let searchState;
358
359 if (!searchStateStr) {
360 // Create a map of zone codes to search for
361 const zoneCodeMap = {};
362 sheetData.forEach(entry => {
363 if (!zoneCodeMap[entry.zoneCode]) {
364 zoneCodeMap[entry.zoneCode] = [];
365 }
366 zoneCodeMap[entry.zoneCode].push(entry);
367 });
368
369 // Get current page from URL
370 const urlParams = new URLSearchParams(window.location.search);
371 const currentPageFromUrl = parseInt(urlParams.get('pNum')) || 1;
372
373 searchState = {
374 currentPage: currentPageFromUrl,
375 totalPages: 815, // FULL MODE: Search all pages
376 pageNumberMap: {},
377 zoneCodeMap: zoneCodeMap,
378 isSearching: true
379 };
380 } else {
381 searchState = JSON.parse(searchStateStr);
382 // Update current page from URL
383 const urlParams = new URLSearchParams(window.location.search);
384 const currentPageFromUrl = parseInt(urlParams.get('pNum')) || 1;
385 searchState.currentPage = currentPageFromUrl;
386 }
387
388 const { currentPage, totalPages, pageNumberMap, zoneCodeMap } = searchState;
389
390 console.log(`Processing page ${currentPage}/${totalPages}...`);
391
392 // Get entries from current page
393 const entries = getCurrentPageEntries();
394 console.log(`Found ${entries.length} entries on page ${currentPage}`);
395
396 // Check each entry against our zone codes
397 entries.forEach(entry => {
398 if (zoneCodeMap[entry.zoneFrom]) {
399 // Found a match! Record the page number for all rows with this zone code
400 zoneCodeMap[entry.zoneFrom].forEach(sheetEntry => {
401 pageNumberMap[sheetEntry.rowIndex] = currentPage;
402 });
403 console.log(`Match found: ${entry.zoneFrom} on page ${currentPage}`);
404 }
405 });
406
407 // Update status
408 const foundCount = Object.keys(pageNumberMap).length;
409 updateStatusPanel(currentPage, totalPages, foundCount, sheetData.length);
410
411 // Save progress BEFORE navigating
412 searchState.pageNumberMap = pageNumberMap;
413 searchState.currentPage = currentPage + 1;
414 await GM.setValue('searchState', JSON.stringify(searchState));
415 console.log(`Saved progress: page ${currentPage}, found ${foundCount}`);
416
417 // Move to next page or finish
418 if (currentPage < totalPages) {
419 // Navigate to next page
420 console.log(`Navigating to page ${currentPage + 1}...`);
421 await new Promise(resolve => setTimeout(resolve, 500));
422 await goToPage(currentPage + 1);
423 } else {
424 // Search complete!
425 console.log('Search complete!');
426 console.log('Page number map:', pageNumberMap);
427
428 // Store the final results
429 await GM.setValue('pageNumberMap', JSON.stringify(pageNumberMap));
430
431 // Clear search state
432 await GM.deleteValue('searchState');
433
434 // Show completion message
435 showCompletionMessage(pageNumberMap, sheetData.length);
436 }
437 }
438
439 function getCurrentPageEntries() {
440 const entries = [];
441 const rows = document.querySelectorAll('#AutoNumber3 tr');
442
443 for (let i = 1; i < rows.length; i++) {
444 const inputs = rows[i].querySelectorAll('input[name="rateZoneFrom"], input[name="rateZoneTo"], input[name="rateZoneRate"]');
445 if (inputs.length >= 3) {
446 entries.push({
447 zoneFrom: inputs[0].value,
448 zoneTo: inputs[1].value,
449 rate: parseFloat(inputs[2].value)
450 });
451 }
452 }
453
454 return entries;
455 }
456
457 async function goToPage(pageNumber) {
458 const pageSelect = document.querySelector('select[name="pageNo"]');
459 if (pageSelect) {
460 pageSelect.selectedIndex = pageNumber - 1;
461 pageSelect.dispatchEvent(new Event('change', { bubbles: true }));
462
463 // Alternative: navigate directly
464 const url = `https://manage.mylimobiz.com/admin/manageRateZones.asp?stab=rateManagement&action=batchEdit&fixedRateMatrixId=18105&rateZoneFrom=&rateZoneTo=&rateZoneVehType=&filter=&pNum=${pageNumber}&pageSize=50`;
465 window.location.href = url;
466 }
467 }
468
469 function createLimoUI() {
470 // Create status panel
471 const panel = document.createElement('div');
472 panel.id = 'limo-status-panel';
473 panel.style.cssText = `
474 position: fixed;
475 top: 20px;
476 right: 20px;
477 width: 350px;
478 background: white;
479 border: 2px solid #1a73e8;
480 border-radius: 8px;
481 box-shadow: 0 4px 16px rgba(0,0,0,0.2);
482 z-index: 10000;
483 font-family: Arial, sans-serif;
484 padding: 20px;
485 `;
486
487 panel.innerHTML = `
488 <h3 style="margin: 0 0 15px 0; color: #1a73e8;">Limoanywhere Search</h3>
489 <div id="limo-status-content">
490 <p>Initializing...</p>
491 </div>
492 <button id="limo-start-button" style="
493 width: 100%;
494 background: #1a73e8;
495 color: white;
496 border: none;
497 border-radius: 4px;
498 padding: 10px;
499 font-size: 14px;
500 font-weight: 500;
501 cursor: pointer;
502 margin-top: 15px;
503 ">Start Search</button>
504 `;
505
506 document.body.appendChild(panel);
507
508 // Add button handler
509 document.getElementById('limo-start-button').addEventListener('click', async () => {
510 document.getElementById('limo-start-button').disabled = true;
511 document.getElementById('limo-start-button').textContent = 'Searching...';
512 await searchLimoanywhere();
513 });
514
515 // Check if there's a search in progress and auto-resume
516 GM.getValue('searchState').then(searchStateStr => {
517 if (searchStateStr) {
518 const searchState = JSON.parse(searchStateStr);
519 if (searchState.isSearching) {
520 console.log('Resuming search automatically...');
521 document.getElementById('limo-start-button').disabled = true;
522 document.getElementById('limo-start-button').textContent = 'Searching...';
523 setTimeout(() => {
524 searchLimoanywhere();
525 }, 1000);
526 }
527 }
528 });
529
530 console.log('Limoanywhere UI created');
531 }
532
533 function updateStatusPanel(currentPage, totalPages, foundCount, totalCount) {
534 const content = document.getElementById('limo-status-content');
535 if (content) {
536 const percentage = Math.round((currentPage / totalPages) * 100);
537 content.innerHTML = `
538 <p><strong>Progress:</strong> Page ${currentPage} of ${totalPages} (${percentage}%)</p>
539 <p><strong>Found:</strong> ${foundCount} of ${totalCount} entries</p>
540 <div style="width: 100%; background: #e0e0e0; border-radius: 4px; height: 20px; margin-top: 10px;">
541 <div style="width: ${percentage}%; background: #1a73e8; height: 100%; border-radius: 4px; transition: width 0.3s;"></div>
542 </div>
543 `;
544 }
545 }
546
547 function showCompletionMessage(pageNumberMap, totalCount) {
548 const content = document.getElementById('limo-status-content');
549 if (content) {
550 const foundCount = Object.keys(pageNumberMap).length;
551 content.innerHTML = `
552 <p style="color: #0f9d58; font-weight: bold;">✓ Search Complete!</p>
553 <p><strong>Found:</strong> ${foundCount} of ${totalCount} entries</p>
554 <p style="margin-top: 15px;">The page numbers have been saved. You can now return to Google Sheets to write them to column H.</p>
555 <button id="copy-results-button" style="
556 width: 100%;
557 background: #0f9d58;
558 color: white;
559 border: none;
560 border-radius: 4px;
561 padding: 10px;
562 font-size: 14px;
563 font-weight: 500;
564 cursor: pointer;
565 margin-top: 10px;
566 ">Copy Results to Clipboard</button>
567 `;
568
569 document.getElementById('copy-results-button').addEventListener('click', async () => {
570 const resultsText = JSON.stringify(pageNumberMap, null, 2);
571 await GM.setClipboard(resultsText);
572 alert('Results copied to clipboard!');
573 });
574 }
575 }
576
577 // ========== INITIALIZATION ==========
578
579 function init() {
580 if (document.readyState === 'loading') {
581 document.addEventListener('DOMContentLoaded', init);
582 return;
583 }
584
585 // Check if we're on Google Sheets
586 if (window.location.href.includes('docs.google.com/spreadsheets')) {
587 console.log('On Google Sheets page, initializing...');
588 setTimeout(() => {
589 createSheetsUI();
590 }, 2000);
591 }
592
593 // Check if we're on Limoanywhere
594 else if (window.location.href.includes('manage.mylimobiz.com')) {
595 console.log('On Limoanywhere page, initializing...');
596 setTimeout(() => {
597 createLimoUI();
598 }, 2000);
599 }
600 }
601
602 init();
603})();