Spreadsheet Worker

makes working spreadsheet projects easier
目前為 2020-04-24 提交的版本，檢視最新版本。
安裝腳本 ?
換行
// ==UserScript==
// @name             Spreadsheet Worker
// @namespace        https://gf.qytechs.cn/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://gf.qytechs.cn/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://gf.qytechs.cn/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>`;
    }
})();
欢迎访问Greasy Fork镜像
