Home / Widgets / Warranty Product Google Sheet Scripts
Duplicate Snippet

Embed Snippet on Your Site

Warranty Product Google Sheet Scripts

Code Preview
js
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}`;
}

Comments

Add a Comment