☰

Spreadsheet worker

makes working spreadsheet projects easier

目前為 2020-04-21 提交的版本，檢視 最新版本。

// ==UserScript==
// @name             Spreadsheet worker
// @namespace        https://greasyfork.org/en/users/77740-nathan-fastestbeef-fastestbeef
// @version          0.01
// @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 = {};
    var currentRow = 0;

    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";

        currentRow=0;
        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() {
        currentRow++;
        while(typeof sheetData.values[currentRow] !== "undefined") {
            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("done");
    }

    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(()=>{currentRow=0;});
            $("#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 />
  </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>`;
    }
})();