makes working spreadsheet projects easier
As of
// ==UserScript==
// @name Spreadsheet worker
// @namespace https://greasyfork.org/en/users/77740-nathan-fastestbeef-fastestbeef
// @version 0.02
// @description makes working spreadsheet projects easier
// @author FastestBeef
// @include https://www.waze.com/editor*
// @include https://www.waze.com/*/editor*
// @include https://beta.waze.com/editor*
// @include https://beta.waze.com/*/editor*
// @exclude https://www.waze.com/*user/editor*
// @grant none
// @require https://greasyfork.org/scripts/24851-wazewrap/code/WazeWrap.js
// ==/UserScript==
/* global W */
/* ecmaVersion 2017 */
/* global $ */
/* global WazeWrap */
(function() {
'use strict';
const CAMPAIGNS = [
{campaignName:'PLN RRC (1)', spreadsheetId:'16PN59_ktx-MiHNzAjbicEebUhw_tyXOK94svIQRqZ-U', sheetName:'Batch%201%20-%20coords', maxRows:'3500', lonCol:'A', latCol:'B', stateCol:'G', betaOnly:false, active:true },
{campaignName:'PLN RRC (2)', spreadsheetId:'16PN59_ktx-MiHNzAjbicEebUhw_tyXOK94svIQRqZ-U', sheetName:'Batch%202%20-%20coords', maxRows:'3500', lonCol:'A', latCol:'B', stateCol:'G', betaOnly:false, active:true },
{campaignName:'PLN RRC (3)', spreadsheetId:'16PN59_ktx-MiHNzAjbicEebUhw_tyXOK94svIQRqZ-U', sheetName:'Batch%203%20-%20coords', maxRows:'3500', lonCol:'A', latCol:'B', stateCol:'G', betaOnly:false, active:true },
{campaignName:'PLN RRC (4)', spreadsheetId:'16PN59_ktx-MiHNzAjbicEebUhw_tyXOK94svIQRqZ-U', sheetName:'Batch%204%20-%20coords', maxRows:'3500', lonCol:'A', latCol:'B', stateCol:'G', betaOnly:false, active:true },
];
const STATES = [
{name:'Iowa', value:'Iowa'},
{name:'Kansas', value:'Kansas'},
{name:'Minnesota', value:'Minnesota'},
{name:'Missouri', value:'Missouri'},
{name:'Nebraska', value:'Nebraska'},
{name:'North Dakota', value:'North Dakota'},
{name:'South Dakota', value:'South Dakota'},
];
var settings = {};
var sheetData = {};
var tab = {};
function getAllRowData() {
let campaignRow=document.getElementById('swCampaignSelect').value;
let spreadsheetId=CAMPAIGNS[campaignRow].spreadsheetId;
let sheetName=CAMPAIGNS[campaignRow].sheetName;
let maxRows=CAMPAIGNS[campaignRow].maxRows;
let apiKey = localStorage.getItem('SW_API_KEY')
if( apiKey === '' ) {
alert('You must set an API key before using this script');
return;
}
let url = "https://sheets.googleapis.com/v4/spreadsheets/"+spreadsheetId+"/values/"+sheetName+"!A1:J"+maxRows+"?key="+apiKey;
console.log("SW: getting sheet info ("+url+")");
fetchAsync(url);
return "https://docs.google.com/spreadsheets/d/1mWH73Z0EYEXmw-BhMHxTfTnhCF-TnFsMRuv0PbjU5es/edit?usp=sharing";
document.getElementById('swCurRow').value = 1;
}
async function fetchAsync (url) {
let response = await fetch(url);
sheetData = await response.json();
return sheetData;
}
/***
0"lon",
1"lat",
2"Beta Editor",
3"Livemap link",
4"Street",
5"City",
6"State",
7"ZIP",
8"Editor Verified",
9"Editor Comments"
***/
function getNext() {
let currentRow = document.getElementById('swCurRow').value * 1;
while(typeof sheetData.values[currentRow] !== "undefined" && currentRow < 500000 ) {
if( typeof sheetData.values[currentRow][8] === "undefined" && (sheetData.values[currentRow][6] === $('#swStateFilter').val() || '' === $('#swStateFilter').val())) {
let lon = sheetData.values[currentRow][0];
let lat = sheetData.values[currentRow][1];
console.log("SW: Row="+(currentRow+1)+" Lon="+lon+" Lat="+lat);
var location = OpenLayers.Layer.SphericalMercator.forwardMercator(parseFloat(lon), parseFloat(lat));
W.map.getOLMap().zoomTo(9);
W.map.setCenter(location);
document.getElementById('swCurRow').value = currentRow+1;
return;
}
currentRow++;
}
alert("No more rows found.");
}
function bootstrap(tries = 1) {
if (W &&
W.map &&
W.model &&
W.loginManager.user &&
$ && WazeWrap.Ready) {
init();
}
else if (tries < 1000) {
setTimeout(function () {bootstrap(tries++);}, 200);
}
}
bootstrap();
function init(){
console.log("SW: Spreadsheet Worker Initializing.");
tab = new WazeWrap.Interface.Tab("SW", tabHTML(),
function (){
$("#swGetNextBtn").click(()=>{getNext()});
CAMPAIGNS.forEach(function(item, i){
var opt = document.createElement('option');
opt.value = i;
opt.innerHTML = item.campaignName;
document.getElementById('swCampaignSelect').appendChild(opt);
});
STATES.forEach(function(item, i){
var opt = document.createElement('option');
opt.value = item.value;
opt.innerHTML = item.name;
document.getElementById('swStateFilter').appendChild(opt);
});
$("#swStateFilter").change(()=>{document.getElementById('swCurRow').value = 1;});
$("#swCampaignSelect").change(()=>{getAllRowData()});
});
if (!localStorage.getItem('SW_API_KEY')) {
localStorage.setItem('SW_API_KEY', '');
}
console.log("SW: Spreadsheet Worker Initialized.");
}
function tabHTML(){
return `
<div id='swTab'>
<div style='display: block' >
<label for='swCampaignSelect'>Campaign</label>
<select id='swCampaignSelect'>
<option value=''>Select</option>
</select>
</div>
<div style='display: block' >
<label for='swStateFilter'>Filter State</label>
<select id='swStateFilter'>
<option value=''>None</option>
</select>
</div>
<div style='display: block' >
<button id='swGetNextBtn'>Next</button>
<label for='swCurRow'>Current Row</label>
<input id='swCurRow' size=10 value=1 />
</div>
<div style='display: block' >
<button id='swAPIKeyUpdate' onClick="localStorage.setItem('SW_API_KEY', document.getElementById('swAPIKey').value)">Update API key</button>
<input id='swAPIKey' />
</div>
<div>
<ol>
<li>Go to <a href='https://console.cloud.google.com/projectselector2/apis/credentials'>Google Cloud Console</a></li>
<li>Select create a project</li>
<li>Give it any name you want and click create</li>
<li>Click create credentials -> API Key</li>
<li>Click Dashboard on the left</li>
<li>Click enable APIs and Services</li>
<li>Find Google Sheets API and click it</li>
<li>Click Enable.</li>
<li>Done. You should be able to use the script. It may take a few minutes for the changes to take effect</li>
</ol>
</div>
</div>`;
}
})();