Warranty Product Google Sheet Scripts

const clientId = "913975986247-4v5j5nhs493nnnolshuq6lt5lr1n3mkq.apps.googleusercontent.com";

const clientSecrect = "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";

const refreshToken = "1//0eutwTFM5cwtcCgYIARAAGA4SNwF-L9IrvX-TJj0leb53IpVSx_z7d_tUfrWsVT1kT13VVT9qUlQnMJ5wOnWq8LpNK_tkilrYkZE";

const form = document.getElementById("warranty-search-form");

const resultsContainer = document.getElementById("warranty-result");

if(form) {

	form.addEventListener("submit", async (e) => {

		e.preventDefault();

		if(grecaptcha.getResponse() !== ""){

			resultsContainer.innerHTML = '<div class="loader-container"><span class="loader"></span></div>';

			const searchInput = document.getElementById("warranty-serial-input");

// 			console.log(searchInput.value);

			await searchWarranty(searchInput.value.trim());

	// 		resultsContainer.textContent = results;   

		} else {

			 resultsContainer.innerHTML =  `<p class="warranty-card__not-found">Xin xác nhận bạn không phải là người máy!</p>` 

		}

	})

}

const searchWarranty = async function(input) {

let accessToken;

// API endpoint and payload

const url = "https://sheets.googleapis.com/v4/spreadsheets/1VHxfao7-rKb12jpKLrt-JmCBG0D75Lt3cwuUvGEfKHw/values/LOOKUP_SHEET!A1:G?includeValuesInResponse=true&responseValueRenderOption=UNFORMATTED_VALUE&valueInputOption=USER_ENTERED&fields=updatedData";

const data = {

  range: "LOOKUP_SHEET!A1:G",

  values: [

    [

      `=FILTER(Sheet1!A1:G; REGEXMATCH(Sheet1!B1:B; "${input}"))`

    ]

  ]

};

const accessTokenCreated = JSON.parse(localStorage.getItem("ggSheetAccessToken"))?.created_at;

const accessTokenCreatedTime = accessTokenCreated ? new Date(accessTokenCreated) : new Date();

const accessTokenExpiredTime = JSON.parse(localStorage.getItem("ggSheetAccessToken"))?.expires_in;

// console.log(localStorage.getItem("ggSheetAccessToken"));

// console.log(JSON.parse(localStorage.getItem("ggSheetAccessToken")));

// console.log(accessTokenCreatedTime);

// console.log(accessTokenExpiredTime);

// console.log(accessTokenCreated !== undefined);

// console.log(accessTokenCreatedTime.setSeconds(accessTokenCreatedTime.getSeconds() + JSON.parse(localStorage.getItem("ggSheetAccessToken")).expires_in) < new Date());

// console.log((accessTokenCreatedTime.getSeconds() + JSON.parse(localStorage.getItem("ggSheetAccessToken")).expires_in > new Date());

// Check if local stograge not have accesstoken or access token expired

if(accessTokenCreated === null || (accessTokenCreatedTime.setSeconds(accessTokenCreatedTime.getSeconds() + accessTokenExpiredTime ? accessTokenExpiredTime : 1 )) < new Date() )	{

// Generate new access token

await refreshAccessToken(refreshToken)

    .then(newAccessToken => {

// Use the new access token for API calls

//console.log('New Access Token:', newAccessToken);

localStorage.setItem("ggSheetAccessToken",JSON.stringify(newAccessToken))

// 		accessToken = newAccessToken;

    })

    .catch(error => {

        console.error('Error refreshing token:', error);

    });

}

accessToken = JSON.parse(localStorage.getItem("ggSheetAccessToken"))?.access_token;

let warrantyResult;

// Send the PUT request to googlesheet create filter and get results back

fetch(url, {

  method: "PUT",

  headers: {

    "Authorization": `Bearer ${accessToken}`,

    "Content-Type": "application/json"

  },

  body: JSON.stringify(data)

})

  .then(response => {

    if (!response.ok) {

      throw new Error(`HTTP error! status: ${response.status}`);

    }

    return response.json();

  })

  .then(result => {

//     console.log("Success:", result);

	warrantyResult = result.updatedData.values;

// 	console.log("warrantyResult is: ", warrantyResult);

	resultsContainer.innerHTML = createWarrantyTable(warrantyResult);

  })

  .catch(error => {

    console.error("Error:", error);

	console.log(error)

  });

// return warrantyResult;

}

// Function to refresh the access token using the refresh token

async function refreshAccessToken(refreshToken) {

    const url = 'https://oauth2.googleapis.com/token';

    // Prepare the request body with the required parameters

    const requestBody = new URLSearchParams({

        client_id: `${clientId}`,        // Replace with your OAuth Client ID

        client_secret: `${clientSecrect}`, // Replace with your OAuth Client Secret

        refresh_token: refreshToken,         // The refresh token you obtained

        grant_type: 'refresh_token'          // Indicating it's a refresh token request

    });

    // Send the POST request to the token endpoint

    const response = await fetch(url, {

        method: 'POST',

        headers: {

            'Content-Type': 'application/x-www-form-urlencoded',

        },

        body: requestBody

    });

    // Handle the response

    if (response.ok) {

        const data = await response.json();

		data.created_at = new Date();

// 		console.log(data);

        const newAccessToken = data;

//         console.log('New Access Token:', newAccessToken);

        return newAccessToken; // Return the new access token

    } else {

        const error = await response.json();

        console.error('Error refreshing token:', error);

        throw new Error('Failed to refresh access token');

    }

}

const createWarrantyTable = function(data) {

let rowHtml = ``;

for(const result of data) {

// 	rowHtml = rowHtml + `

// <tr>

// 		<th class="warraty-table-row-data-header">${result[0] === "#N/A (No matches are found in FILTER evaluation.)" ? "Không tìm thấy kết quả" : result[0]}</th>

// 		<td class="warraty-table-data">${result[0] === "#N/A (No matches are found in FILTER evaluation.)" ? "" : result[1]}</td>

// 		<td class="warraty-table-data">${result[0] === "#N/A (No matches are found in FILTER evaluation.)" ? "" : result[2]}</td>

// 		<td class="warraty-table-data">${result[0] === "#N/A (No matches are found in FILTER evaluation.)" ? "" : convertToDate(result[3])}</td>

// 		<td class="warraty-table-data">${result[0] === "#N/A (No matches are found in FILTER evaluation.)" ? "" : convertToDate(result[5])}</td>

// 		<td class="warraty-table-data warraty-table-data--status ${ result[6] === "Còn bảo hành" ? "warraty-table-data--status--under-warranty" : "warraty-table-data--status--exprired"}">${result[0] === "#N/A (No matches are found in FILTER evaluation.)" ? "" : result[6]}</td>

// 		</tr>

// ` 

// 

// 

// 

const productName = result[2] ? result[2].replace(/\s{2,}/g, ' ').toLowerCase() : "";

const productImage = productName.includes("tấm pin") ? "https://phucdattech.com.vn/wp-content/uploads/2024/05/5_412_612_f1b4307b5e.png" :

productName.toLowerCase().includes("sma 3 pha") ? "https://phucdattech.com.vn/wp-content/uploads/2020/10/SMA-15-25-kWP-phuc-dat.png" :

productName.toLowerCase().includes("sungrow") ? "https://phucdattech.com.vn/wp-content/uploads/2020/01/sungrow10-20kwp.png" :

productName.toLowerCase().includes("hòa lưới mg") ? "https://phucdattech.com.vn/wp-content/uploads/2024/05/inverter-invt-imars-mg-5kw-1510-1.png" : "https://phucdattech.com.vn/wp-content/uploads/2021/08/cropped-PD-2021.png";

if(result[0] === "#N/A (No matches are found in FILTER evaluation.)") {

rowHtml = `<p class="warranty-card__not-found">Không tìm thấy kết quả. Xin kiểm tra lại số serial của bạn!</p>`

} else {

rowHtml = rowHtml + `

<div class="warranty-card">

	<div class="warranty-card__general-info-container">

		<div class="warranty-card__image-container">

					<img class="warranty-card__product-image" src=${productImage}} alt="product image">

		</div>

		<div class="warranty-card__general-info-content">

			<p class="warranty-card__product-name">

				${result[2]}

			</p>

			<p class="warranty-card__product-purchased-date">

				Ngày mua hàng: ${convertToDate(result[3])}

			</p>

			<p class="warranty-card__product-serial">

				Serial Number: ${result[1]}

			</p>

		</div>

	</div>

	<div class="warranty-card__warranty-detail-container">

		<p class="warranty-card__header">

			Thông tin bảo hành

		</p>

		<div class="warranty-card__warranty-detail-content">

			<p class="warranty-card__warranty-status ${result[6] === "Còn bảo hành" ? "warranty-card__warranty-status--active": "warranty-card__warranty-status--expired"} ">

				${result[6]}

			</p>

			<p class="warranty-card__warranty-type">

				Bảo hành có thời hạn

			</p>

			<p class="warranty-card__warranty-expired-date">

				Ngày hết hạn: ${convertToDate(result[5])}

			</p>

		</div>

	</div>

</div>

`

}

}

	const tableHtml = rowHtml;

	return tableHtml;

}

// Function to convert Google Sheets date number to ddmmyyyy

function convertToDate(googleSheetDate) {

    const date = new Date((googleSheetDate - 25569) * 86400 * 1000);  // Convert serial to JavaScript Date

    const day = String(date.getDate()).padStart(2, '0');   // Day in two-digit format

    const month = String(date.getMonth() + 1).padStart(2, '0');  // Month in two-digit format (0-based index)

    const year = date.getFullYear();  // Full year

    // Format as ddmmyyyy

    return `${day}/${month}/${year}`;

}