makes working spreadsheet projects easier
Per
// ==UserScript==
// @name Spreadsheet Worker
// @namespace https://greasyfork.org/en/users/77740-nathan-fastestbeef-fastestbeef
// @version 2019.04.24
// @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 */
/* global OpenLayers */
(function() {
'use strict';
const VERSION = GM_info.script.version;
const SCRIPT_NAME = GM_info.script.name;
const UPDATE_NOTES = "<p><ul>You can now use the hotkey 'n' to get the next row.</ul></p>";
const CAMPAIGN_SHEET_ID = "1vy_28mDW8CDIUUXbUG0XZgCNX7iJ6sdPEtbCvQvXrLs";
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', abbr:'IA'},
{name:'Kansas', value:'kansas', abbr:'KS'},
{name:'Minnesota', value:'minnesota', abbr:'MN'},
{name:'Missouri', value:'missouri', abbr:'MO'},
{name:'Nebraska', value:'nebraska', abbr:'NE'},
{name:'North Dakota', value:'north dakota', abbr:'ND'},
{name:'South Dakota', value:'south dakota', abbr:'SD'},
];
let settings = {};
let sheetData = {};
let tab = {};
let campaigns = [];
function getCampaignData() {
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 === '' ) {
WazeWrap.Alerts.error('Spreadsheet Worker', 'You must set an API key before using this script');
return;
}
let url = "https://sheets.googleapis.com/v4/spreadsheets/"+CAMPAIGN_SHEET_ID+"/values/Sheet1!A1:I500?key="+apiKey;
console.log("SW: getting sheet info ("+url+")");
fetchCampaignData(url);
}
async function fetchCampaignData (url) {
let response = await fetch(url);
//Campaign Name Spreadsheet ID Sheet Name Max Rows Lon Col Lat Col State Col Beta Only Active
campaigns = await response.json();
return sheetData;
}
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 === '' ) {
WazeWrap.Alerts.error('Spreadsheet Worker', '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+")");
fetchRowData(url);
document.getElementById('swCurRow').value = 1;
}
async function fetchRowData (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].toLowerCase() === $('#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++;
}
WazeWrap.Alerts.info("Spreadsheet Worker", "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', '');
}
new WazeWrap.Interface.Shortcut("Next Row", "Get next row from spreadsheet worker script", "wmessw", "WME Spreadsheet Worker", "n", function(){getNext();}, null).add();
WazeWrap.Interface.ShowScriptUpdate(SCRIPT_NAME, VERSION, UPDATE_NOTES, "https://greasyfork.org/en/scripts/401655-spreadsheet-worker", "");
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>`;
}
})();