backup.js

import '../App.css';
import React, { useState, useEffect } from "react";
import { makeStyles } from '@material-ui/core/styles';

const useStyles = makeStyles((theme) => ({    //page styling
    background: {
        backgroundColor: "#2E2E2E", 
        height: "100%", 
        width: "100%", 
        display: "flex", 
        justifyContent: "center"
    },
    navbar: {
        position: "absolute",
        backgroundColor: "#2E2E2E",
        height: "35px",
        width: "100%",
        top: "0px",
        display: "flex",
        justifyContent: "center",
        alignItems: "center",
        boxShadow: "0px 2px 8px black"
    },
    logoSurround: {
        position: "absolute", 
        backgroundColor: "white", 
        height: "35px", 
        width: "200px", 
        top: "0px", 
        display: "flex", 
        justifyContent: "center", 
        alignItems: "center", 
        borderRadius: "0px 0px 10px 10px"
    },
    logoutButton: {
        backgroundColor: "white", 
        textAlign:"center", 
        fontSize:"15pt", 
        color:"black", 
        border:" solid white", 
        borderRadius: "10px", 
        height:"35px", 
        width:"120px", 
        marginTop:"100px", 
        marginLeft:"60px", 
        cursor: 'pointer',
    },
    cleanDataButton: {
        backgroundColor: "white", 
        textAlign:"center", 
        fontSize:"15pt", 
        color:"black", 
        border:" solid white", 
        borderRadius: "10px", 
        height:"35px", 
        width:"200px", 
        marginTop:"100px", 
        marginLeft:"60px", 
        cursor: 'pointer',
    },
    spreadsheetDisplay: {
        marginTop: '100px',
        backgroundColor:'#2E2E2E',
    },
    table: {
        marginTop: "10px",
        marginLeft: "150px",
        backgroundColor: "white",
        color:"black",
        width:'1200px',
        height: "40px",
        borderCollapse: "collapse",
        overflow: "scroll",
    },
    tableHeaders: {
        border: "2px black solid",
    },
    tableRows: {
        border:"1px black solid",
        
    },
    dropdown: {
        marginTop: "100px",
        backgroundColor: "white", 
        textAlign:"center", 
        fontSize:"15pt", 
        color:"black", 
        border:" solid white", 
        borderRadius: "10px", 
        height:"35px", 
        width:"150px", 
        marginLeft:"60px", 
        cursor: 'pointer',
    },
    dropdownText: {
        color:'red'
    }
}));

const userID=localStorage.getItem('userid')        //local storage gives userid and token of current user logged in
const token=localStorage.getItem('authorization')
const { GoogleSpreadsheet } = require('google-spreadsheet');
const creds = require('./client_secret.json');
const doc = new GoogleSpreadsheet('1hd-30zjeEh0lRdwQsVAaaFWTc5mJngUyBg70O7EWA80'); //spreadsheet ID

const handleLogout = () => {        //handle logout when the logout button is clicked
        localStorage.removeItem('userid')
        localStorage.removeItem('authorization')     //removes localStorage to remove access to secure pages
        localStorage.removeItem('username')
        window.location.href='/'
        alert("Your have logged out successfully")   //give alert to user that they are logged out
    };


export function App() {      //here the classes is assigned to get access to the styling
    const classes = useStyles();
    const [rows, setRows] = useState([]);
    const [headers, setHeaders] = useState([]);
    var count
    useEffect(async() => {
        await doc.useServiceAccountAuth({
            client_email: creds.client_email,
            private_key: creds.private_key,
        });
        await doc.loadInfo(); // loads document properties and worksheets
        console.log(doc.title);
        console.log(doc)
        var jsonData = doc._rawSheets;
        console.log(jsonData)
        count = Object.keys(jsonData).length;
        console.log(count);
        let preSpreadsheetName = []
        for(let i = 0; i < count; i++){
            preSpreadsheetName.push(doc.sheetsByIndex[i]);
        };
        let finalSpreadsheetName = ['Choose ']
        for(let j = 0; j < count; j++){
            finalSpreadsheetName.push((preSpreadsheetName[j]._rawProperties).title)
        };
        console.log(finalSpreadsheetName)
        var dataHeaders = jsonData._rawProperties;
        console.log(dataHeaders);
        var values = finalSpreadsheetName;
        var select = document.createElement("select");
        select.name = "sheetNames";
        select.id = "sheetNames"
        for (const val of values)
        {
            console.log(val)
            var option = document.createElement("option");
            option.currentSheet = val;
            option.text = val;
            select.appendChild(option);
        }
        document.getElementById("container").appendChild(select)
    },[]);
    let currentSheet
    let currentSheetNew
    async function importSheet(){
        var select = document.getElementById('sheetNames');
        currentSheetNew = select.options[select.selectedIndex].currentSheet;
        console.log("This is the current sheet: " + currentSheetNew);
        const sheet = doc.sheetsByTitle[currentSheetNew];// or use doc.sheetsById[id]
        console.log(sheet.title);
        console.log(sheet.rowCount);
        setRows(await sheet.getRows());
        setHeaders(await sheet.headerValues);
        console.log(headers);
        console.log("ROWS");
        console.log(rows);
    }
    //user needs to trim whitespaces and add extra field data before clean and reupload
    //how to do it in regex
    async function cleanData() {
        const regex = /[()]|\suk\s*|\sltd\s*|\sllp\s*|\sirl\s*|\splc\s*|\slimited\s*|\sireland\s*|\semea\s*|\n*|\r*|/gi;
        //\suk\s*|\sltd\s*|\sllp\s*|\sirl\s*|\splc\s*|\slimited\s*|\sireland\s*|\semea\s*|[(?:ltd,?:plc)]|\n*|\r*|
        var result;
        for (let i = 0; i < rows.length; i++){
            const str = rows[i].Company
            const subst = ``;
            // The substituted value will be contained in the result variable
            result = str.replace(regex, subst);
            rows[i].Company = result;
            console.log(rows[i].Company);
            console.log(headers);
        };
    alert('Data cleaned')
    console.log(result);
    }
    async function uploadData() {
        console.log(currentSheet)
        await doc.loadInfo();
        var jsonData = doc._rawSheets;
        console.log(jsonData)
        const sheet = doc.sheetsByTitle[currentSheet];// or use doc.sheetsById[id]
        let newSheetName = sheet.title;
        console.log(sheet.rowCount);
        setRows(await sheet.getRows());
        setHeaders(await sheet.headerValues);
        let preSpreadsheetName = []
        for(let i = 0; i < count; i++){
            preSpreadsheetName.push(doc.sheetsByIndex[i]);
        };
        let finalSpreadsheetName = ['Choose']
        for(let j = 0; j < count; j++){
            if(currentSheet == newSheetName){
                finalSpreadsheetName.push((preSpreadsheetName[j]._rawProperties).title)
            }
        };
        console.log(finalSpreadsheetName)
        const newSheet = await doc.addSheet({ title: "CLD - "+finalSpreadsheetName, headerValues: ['FirstName', 'LastName', 'JobTitle', 'Company', 'Email', "LinkedInProfile",  "Campaign",	"Delivery",	
                                                "Vertical",	"ProspectinComplete",	"Phone",	"Phone1",	"Owner",	"HubspotUploadOwner",
                                                "HubspotID",	"CurrentContract1",	"CurrentContract2",	"CurrentContract3",	"CurrentContract4",	"CurrentICP",	"NewContract",	"NewICP",
                                                "Createdate",	"DataSource",	"LinkedInListName",	"FinalIndustry"] });
        const moreRows = await newSheet.addRows(rows);
        alert('Data uploaded in the tab called: ' + newSheet.title)
    }
    //need to get Hubspot ID and create date back
    //check if contact already exists, if so, update existing contact
    //return existing contact data (contracts and ICPs)
    async function handleContact(){
        console.log(rows[0].FirstName)
        for(let i = 0 ; i < rows.length; i++){
                try {
                    fetch('https://perfect-subway-5000.codio-box.uk/addContact', {
                        method: 'POST',
                        headers: {
                            'Content-type': 'application/json',
                        },
                        body: JSON.stringify({
                            "firstname": rows[i].FirstName,
                            "lastname": rows[i].LastName,
                            "jobtitle": rows[i].JobTitle,
                            "company": rows[i].Company,
                            "email": rows[i].Email,
                            "hubspot_owner_id": rows[i].HubspotUploadOwner,
                            "icp": rows[i].NewICP
                        })
                    })
                } catch(e) {
                    console.log(e)
                }
            //     try {
            //         fetch('https://perfect-subway-5000.codio-box.uk/updateContact', {
            //         method: 'POST',
            //         headers: {
            //             'Content-type': 'application/json',
            //         },
            //         body: JSON.stringify({
            //             "icp": rows[i].NewICP
            //         })
            //         })
            //     } catch(e) {
            //     console.log(e)
            // }
            
        }
        alert('Number of contacts uploaded: ' + rows.length)
	};
    if (userID!==null){
        return (
            <div>
                <div className={classes.navbar}>
                    <label className={classes.dropdownText}>Choose spreadsheet</label>
                    <div id="container" name="sheetNames" className={classes.dropdown}></div>
                    <button className={classes.cleanDataButton} onClick={importSheet}>Import sheet</button>
                    <button className={classes.logoutButton} onClick={handleLogout}>Log out</button>
                    <button className={classes.cleanDataButton} onClick={cleanData}>Clean data</button>
                    <button className={classes.cleanDataButton} onClick={uploadData}>Upload data</button>
                    <button className={classes.cleanDataButton} onClick={handleContact}>Upload to Hubspot</button>
                    <div className={classes.logoSurround}>
                        <img alt="epik8logo" width="60" height="25" src="epik8newlogo2.png"></img>
                    </div>
                </div>
                <div className={classes.background}>
                    <div className={classes.spreadsheetDisplay}>
                        <table className={classes.table}>
                        <thead>
                            <tr>
                                <th className={classes.tableHeaders}>First Name</th>
                                <th className={classes.tableHeaders}>Last Name</th>
                                <th className={classes.tableHeaders}>Email</th>
                                <th className={classes.tableHeaders}>Company Name</th>
                            </tr>
                        </thead>
            <tbody>
            {rows.map(row => {
                return (
                    <tr key={row.Email}>
                        <td className={classes.tableRows}>{row.FirstName}</td>
                        <td className={classes.tableRows}>{row.LastName}</td>
                        <td className={classes.tableRows}>{row.Email}</td>
                        <td className={classes.tableRows}>{row.Company}</td>
                    </tr>
                );
            })}
            </tbody>
                        </table>
                    </div>
                </div>
            </div>
            
        );
    } else {     //no user is logged in, redirect to login page
        {window.location.href='/login'}
    }
}

export default App;