Conditional formatting in Excel allows users to visually format cells based on specified conditions. While Microsoft Excel seamlessly integrates formatting with icons, providing enhanced and visually appealing spreadsheets, Google Sheets lacks this cool native feature.
<img src="/uploads/2023/11/conditional-formatting-icons-excel.png" alt="Conditional formatting with icons in MS Excel">
<blockquote>
Although we cannot create the icon in the same cell like Excel Sheet but we can achieve similar results with image formula in Google Sheet
</blockquote>
<h2>How it Works</h2>
The image formula helps to render an image form a url which plays the major role rendering the icon.
<pre class="language-javascript"><code>=IF(logical_expression, value_if_true, value_if_false)
=IMAGE(url, [mode], [height], [width]) </code></pre>
You can upload the image to any 3rd party server or cloud and just use the url in the formula
<pre class="language-javascript"><code>=IF(A3&lt;50,IMAGE("your_icon_url",3),IMAGE("your_icon_url",3)) </code></pre>
The above formula will render any one of the two images based on the condition A3&lt;50. You can even use nested IF when you have more than 2 conditions. Here is an example:&nbsp;
<pre class="language-javascript"><code>=IF(D2="Yes",IMAGE("your_icon_url",3),IF(D2="No",IMAGE("your_icon_url",3),IMAGE("your_icon_url",3))) </code></pre>
The above formula will show three different icons based on the cell value "Yes", "No" and "Maybe". Here is a <a href="https://docs.google.com/spreadsheets/d/1BdWhXhhh1GOUT2sE1t8TzDaEFpFXDlFtcwQX2Y8QX0s/edit#gid=1381015584" target="_blank" rel="noopener">Demo Spreadsheet</a> where you can see it all live.
<h2>Change Icon Size</h2>
The =IMAGE("your_icon_url", 2) formula accepts a 2nd parameter which sets the size mode.
<table style="border-collapse: collapse; width: 100%; border-width: 1px; height: 180px;" border="1"><colgroup><col style="width: 49.9795%;"><col style="width: 49.9795%;"></colgroup>
<thead>
<tr style="height: 30px;">
<td style="height: 30px;">Options</td>
<td style="height: 30px;">Example</td>
</tr>
</thead>
<tbody>
<tr style="height: 30px;">
<td style="height: 30px;">Size to fit</td>
<td style="height: 30px;">=IMAGE("your_icon_url", 1)</td>
</tr>
<tr style="height: 30px;">
<td style="height: 30px;">Stretch to fit</td>
<td style="height: 30px;">=IMAGE("your_icon_url", 2)</td>
</tr>
<tr style="height: 30px;">
<td style="height: 30px;">Original size</td>
<td style="height: 30px;">=IMAGE("your_icon_url", 3)</td>
</tr>
<tr style="height: 60px;">
<td style="height: 60px;">Custom size</td>
<td style="height: 60px;">=IMAGE("your_icon_url", 4, height(px), width(px))</td>
</tr>
</tbody>
</table>
<img src="/uploads/2023/11/image-formula.png" alt="Image formula with size parameter">
<h2>Put the Icon and Value in the Same Cell</h2>
In Google Sheet it is not possible to put the icon and value in same cell similar to Excel sheet, however there is a workaround. We can put the text and the icon side by side and have right the border same as the background. <img src="/uploads/2023/11/same-cell-icons.png">
<h2>Formatting with SPARKILINE</h2>
Sparkline is a cute feature available in Google Sheets, designed to create tiny bar charts directly within cells. These tiny, in-cell charts provide a quick overview of data variations, making it easier to interpret large datasets at a glance.
<pre class="language-javascript"><code>=SPARKLINE(E2,{"charttype","bar";"max",100})</code></pre>
<img src="/uploads/2023/11/sparkline.png">
<h3>SPARKLINE Syntax</h3>
<pre class="language-javascript"><code>=SPARKLINE(data, [options])</code></pre>
<ul>
<li>data - The range or array containing the data to plot.</li>
<li>options - [ OPTIONAL ] - A range or array of optional settings and associated values used to customize the chart.</li>
</ul>
<h3>Sample Usage</h3>
<pre class="language-javascript"><code>SPARKLINE(A1:F1)

SPARKLINE(A2:E2,{"charttype","bar";"max",40})

SPARKLINE(A2:E2,A4:B5)

SPARKLINE(A1:A5, {"charttype","column"; "axis", true; "axiscolor", "red"})</code></pre>

Apply conditional formatting with icon sets in Google Sheets, utilizing a variety of pre-designed icon sets tailored for specific scenarios. Use sparkline to render in-cell bar charts.

How to Apply Conditional Formatting with Icons in Google Sheets

<h1>About Chat Plus</h1>
Chat Plus is a chrome extension developed by Tabgraf that enables additional features in Google Chat some of which are:-
<ol>
<li>Audio message</li>
<li>Reminders</li>
<li>Bookmarking message</li>
<li>Encrypt and Decrypt message</li>
<li>Quoting a message</li>
<li>Code and Code blocks</li>
<li>Location sharing</li>
<li>Copy link of the message</li>
<li>And Counting ...</li>
</ol>
&nbsp;
<h1>Send Audio message in Google Chat</h1>
To send audio message in Google Chat first you need to install <a title="Chat plus" href="https://chromewebstore.google.com/detail/njkkenehdklkfdkmonkagaicllmnfcda?hl=en" target="_blank" rel="noopener">Chat plus</a> from the Chrome webstore. After the extension has been successfully installed you will be finding a lot of additional features has been enabled in Google Chat. Among this is the Audio Messaging. Here is a screenshot attached to help you locate this feature.
<img src="/uploads/2023/10/chat-plus-audio-recorder.png">
&nbsp;
Once you click on the mic, a popup with a recorder will open (for the first time users, you must login with your Google account to use Chat Plus). This recorder will record your audio message and send it to Google Chat as a link which you send it in the chat. An audio preview will also be injected with the link for a quick preview of the message.
<img src="/uploads/2023/10/chat-plus-audio-screen.png">
&nbsp;
<h1>How the Audio messaging works ?</h1>
The moment the record button is clicked, a instance MediaRecorder is created, and event listner `dataavailable` is attched to it, which provides the audio that is being recorder in chunk and that is being collected in chunks array.&nbsp; Here is a reference of how the MediaRecorder works:
<pre class="language-javascript"><code>navigator.mediaDevices.getUserMedia({ audio: true }).then((stream) =&gt; {
 const mediaRecorder = new MediaRecorder(stream);
 let chunks = [];

 mediaRecorder.addEventListener("dataavailable", (event) =&gt; {
 if (event.data.size &gt; 400) {
 if (!animateDiv.classList.contains("animate-recorder")) {
 animateDiv.classList.add("animate-recorder");
 }
 } else {
 animateDiv.classList.remove("animate-recorder");
 }
 chunks.push(event.data);
 });
});
</code></pre>
&nbsp;
<h1>How the audio chunks is converted back to audio&nbsp;?</h1>
The chunks array that is generated during the recording is converted to Blob of type `mp3` which is used to create a audio url. This audio url is again utilised for creating a base64 which is sent to the backend to save a audio file.
<pre class="language-javascript"><code>const audioBlob = new Blob(chunks, { type: "audio/mp3" });
const reader = new FileReader();
reader.readAsDataURL(audioBlob);
reader.onload = () =&gt; {
 const base64AudioMessage = reader.result.split(",")[1];
 console.log(base64AudioMessage);
};
</code></pre>
In the backend a new instance of Buffer is created which converts the `base64AudioMessage` to buffer. This buffer is used to create an audio file with help of node js file system(fs).
<pre class="language-javascript"><code>const buffer = Buffer.from(base64AudioMessage , "base64");

fs.writeFileSync(audioFilePath, buffer);
</code></pre>
So , this how the Audio messaging feature works in Chat Plus.

Google natively does not provide a way to send audio messages in the chat. But with Chat plus chrome extension we can effectively send Audio messages in Google Chat with a lot of other features.

How to send audio message in Google Chat?

<h1>Introduction</h1>
With the growing popularity of YouTube live streaming, the YouTube Live Comments Archiver Extension is a must-have tool for anyone who wants to save or download live chat moments and video comments.
&nbsp;
<h1 dir="ltr">Live Comment Archiving:</h1>
<ul>
<li dir="ltr" aria-level="1">Single-Click Save: With a single click during a live stream, users can instantly save the ongoing live comments. <img src="/uploads/2023/10/live-comments-modal.png"></li>
<li dir="ltr" aria-level="1">Download Progress Indicator: The extension displays a download progress indicator, keeping users informed about the amount of content already saved.</li>
<li dir="ltr" aria-level="1">Delete Downloaded Comments: Clear up space effortlessly by deleting previously saved comments directly from the extension</li>
</ul>
<h1 dir="ltr">&nbsp;</h1>
<h1 dir="ltr">&nbsp;Bulk Comment Archiving:</h1>
<ul>
<li dir="ltr" role="presentation">Save All Comments: Not just limited to live comments, users can save all comments on a video with a dedicated button as shown in the screenshot.</li>
<li dir="ltr" role="presentation">CSV File Download: For comprehensive analysis, users can download all comments in a convenient CSV file format.</li>
</ul>
&nbsp;
<h1>Steps to Download and Use the Plugin</h1>
<ol>
<li>Install the Extension from <a title="Save Live Streaming Chats for YouTube&trade;" href="https://chromewebstore.google.com/detail/save-live-streaming-chats/bcclhcedlelimlnmcpfeiabljbpahnef?hl=en" target="_blank" rel="noopener">Save Live Streaming Chats for YouTube&trade;</a>.</li>
<li>Once installed open any ongoing YouTube livestream of your choice.</li>
<li>The Download Chats button is located directly to the right of Top Chat. When you click on it, it will start collecting comments, and when you click on it again, it will download the comments to your computer. Additionally, you can track the number of saved chats, and if desired, download the saved chat in a CSV file.</li>
</ol>
&nbsp;
A screenshot is provided for your reference.
<img src="/uploads/2023/10/youtube-livechat-demo-banner.png">
&nbsp;
<h1>About the Downloaded CSV File:</h1>
Once you've successfully downloaded the CSV file, you'll discover six insightful columns, each containing valuable information related to your comments. Let's explore the details within these columns:
For Live Comments (7 columns):
<ol>
<li>Author Name:&nbsp;The name of the user who contributed to the live comment.</li>
<li>Comments Text:&nbsp;The actual content of the comment made during the live stream.</li>
<li>Image:&nbsp;The profile picture or image associated with the user who contributed to the live comment.</li>
<li>Time:&nbsp;The timestamp indicates when the live comment was posted.</li>
<li>Author:&nbsp; Author of the live comment.</li>
<li>TimeStamp:&nbsp;The timestamp indicates when the live comment was posted.</li>
<li>VideoTime: Additional details or description related to the playback time at which the live comment was posted.</li>
</ol>
For Comments (7 columns):
<ol>
<li>Comment ID: &nbsp;A unique identifier is assigned to each comment for efficient tracking and referencing.</li>
<li>Parent ID:&nbsp;&nbsp;The ID of the parent comment, indicates the hierarchical structure in the comment thread.</li>
<li>Text:&nbsp;The textual content of the comment.</li>
<li>Time:&nbsp;The timestamp indicates when the comment was posted.</li>
<li>Author:&nbsp;The author or user who posted the comment.</li>
<li>LikeCount: The count of likes received by the comment.</li>
<li>ReplyCount: The count of replies or responses received by the comment.</li>
</ol>
&nbsp;
<h1>Mutation Observers:</h1>
The Mutation Observer is a JavaScript tool that helps your extension stay in sync with the live comments section on YouTube.Lets see how it works:
<pre class="language-javascript"><code>// Behold the Wizardry of Mutation Observers!
const observerConfig = {
 childList: true, // Keep an eye on changes in child nodes
 subtree: true, // Delve into the entire subtree for magical discoveries
};

// The Enchanted Callback Function
const mutationCallback = function (mutationsList, observer) {
 for (const mutation of mutationsList) {
 if (mutation.type === 'childList' &amp;&amp; mutation.addedNodes.length &gt; 0) {
 // Aha! New comments have graced our presence
 console.log('New comments detected:', mutation.addedNodes.length);
 // Unleash your extension's magic&mdash;update the interface or trigger archiving spells
 }
 }
};

// Summoning the Mutation Observer
const commentsObserver = new MutationObserver(mutationCallback);

// Empowering the Observer with Configuration
commentsObserver.observe(document.body, observerConfig);

// When the magic is no longer needed (e.g., when the user ventures elsewhere)
// commentsObserver.disconnect();
</code></pre>
&nbsp;
<h1>Conclusion:</h1>
The YouTube Live Comments Archiver Extension is a powerful tool that lets you save and download live chat moments and video comments on YouTube, so you can capture and relive your favorite moments, or share them with others.

Save live and video comments effortlessly with the YouTube live comments archiver extension—capturing every moment for a richer online experience.

How to Export Youtube Live Comments into CSV

Tracking time you spent on tasks can become quite challenging especially when you're working with multiple tasks across serveral projects. While there are numerous online time trackers available, they often lack the user-friendly simplicity found in a spreadsheet.
Google Spreadsheet is a powerful tool having capabilities in both data storage and analysis, but there's not an effective time tracker available on the market that you can integrate with Google Sheets. Hence we've come up with a tool using&nbsp;<code>Google Apps Script</code> that enables you to keep track of the time right inside the Google Spreadsheet.
Listed below are the key features of Timesheet Tracker.
<ol>
<li>Easy to use, simple and intuitive interface, one-click timer start-stop</li>
<li>Categorize tasks based on projects</li>
<li>Easily generate pivot, charts and data formatting using built-in spreadsheet features</li>
</ol>
<img src="/uploads/2023/11/timesheet-tracker.png" alt="Timesheet Tracker">
<h2>How to Install</h2>
This is a GAS (Google Apps Script) utility and a standalone project. You just need to create a copy of the spreadsheet in your Google Drive and run it from the spreadsheet sidebar.
<ul>
<li>Create a copy of this <a href="https://docs.google.com/spreadsheets/d/1Df-R2pH-nk0xY8rS6OzAb51Ergi0p3f4kWEAgewUonU/copy" target="_blank" rel="noopener">Google Spreadsheet</a></li>
<li>Once the spreadsheet is open, you'll notice a new menu in the toolbar named "Timesheet Tracker"</li>
<li>Create project entries under the <code>Settings sheet</code>. You can also enable a&nbsp;<code>ticking clock sound</code>.</li>
<li>Click on "Timesheet Tracker." &rarr; "Open" to reveal a sidebar where you can start recording time on a new task.</li>
</ul>
<img src="/uploads/2023/11/projects-timesheet-tracker.png" alt="Project list - Timesheet Tracker">
<blockquote>
This tool uses JavaScript setInterval to compute the time spent on a task. However, there are instances where setInterval may not function as intended, especially if you navigate away from the tab. It is recommended to open the spreadsheet in a new window and ensure that the tab remains active.
</blockquote>
<h2>Technologies Used</h2>
As this an open source tool, you can always explore and tweak it according to your requirements. If you need customization, you can&nbsp;<a href="/contact?type=project#contact-form">reach out to us</a>. Here are the technologies used in the script.
<ol>
<li>Vue.js and Bootstrap serves the front-end</li>
<li>Google Apps Script handles the backend</li>
</ol>
The script uses <code>google.script.run</code>, a client-side JavaScript API provided by Google Apps Script, primarily used within Google Workspace applications like Google Sheets, Google Docs, and Google Forms. This API allows client-side code (JavaScript in an HTML file) to call server-side functions (Google Apps Script functions) without needing to refresh the page.
Here's a basic example of how <code>google.script.run</code> is used with Google Apps Script:
<pre class="language-javascript"><code> saveData: function(){
 google.script.run.saveData(this.timerId,this.projectTitle,this.taskTitle,this.displayTime);
 },</code></pre>
<h2>How are New Records Inserted into the Sheet?</h2>
First, the row number is determined by the unique id of the task. If found, the script updates the timeSpent. If not found, a new row is inserted at the top of the spreadshet to make it easy for the user not to scroll always to the bottom of the Spreadsheet to view the latest data.
<pre class="language-javascript"><code>function saveData(id,projectName,taskTitle,timeSpent){
 var spreadSheeet = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = spreadSheeet.getSheetByName("Timesheet");
 var rowNum = findRowNum(sheet,id);
 if(rowNum==-1){
 sheet.insertRowBefore(2);
 sheet.getRange(2, 1, 1, 5).setValues([[id,projectName,taskTitle,new Date(),timeSpent]]);
 }else{
 sheet.getRange("E"+rowNum).setValue(timeSpent);
 }
}</code></pre>
<h2>How the Time is Calculated?</h2>
The <code>setInterval</code> which runs every 1 sec, increases the <code>totalSecs</code> variable only if the current status is <code>running</code>. Every 30 secs, the data is synced with the Spreadsheet to prevent accidental data loss.
<pre class="language-javascript"><code>function startTimer() {
 if (!this.validate()) {
 return;
 }
 this.timerId = new Date().getTime();
 this.timerStatus = "running";
 var self = this;
 if (this.timeInterval == null) {
 this.timeInterval = setInterval(function () {
 if (self.timerStatus == "running") {
 self.totalSecs++;
 if (self.clockSound == "Yes") {
 self.playAudio();
 }
 if (self.totalSecs % 30 == 0) {
 self.saveData();
 }
 }
 }, 1000);
 }
}
</code></pre>

Timesheet Tracker is a Google Spreadsheet tool which allows you to record and manage your time right inside the Google Spreadsheet interface. Easily maintain time for project-specific tasks with its user-friendly interface and leverage the robust capabilities of a spreadsheet for seamless report generation and pivot analysis.

How to Create a Timesheet Tracker in Google Spreadsheet

Track your shop's activities and admin logs in real-time. Keep an eye on your shop.

What *Logify* can do ?

Important Event Logs
Logs the crucial and significant logs occuring in the Store
&nbsp;
Admin Logs
Records the logs created by the Admin of the Shop
&nbsp;
Staff Logs
Records the logs created by the Staff Members of the Shop
&nbsp;
Resource logs
Gives a Graphical Representation of the logs with Heatmap and Bar Chart

Top Features of *Logify*

Important event logs record significant and crucial logs that occur in the Shop which are created by Admin or Staff.

Important Event Logs

These logs capture a wide range of activities taking place within your shop's admin interface, including products, orders, inventories, and checkouts.

Admin Logs

These logs include all activities that your staff members perform in the admin interface.&nbsp;

Staff Logs

Resource logs provide a Graphical visualization of the logs in the form of Heatmap and Bar Chart. The heatmap visually represents the count of logs on a day-by-day basis. The bar chart, on the other hand, shows the activities on the resources at different times of the day.

Resource logs

Capture real-time activities and logs from your store.

Logify

SyncSheet is a MS Excel add-in which essentially syncs data from MS Excel to Google Spreadsheet and vice-versa. You can easily set up a bidirectional data synchronization that transfers data while preserving formatting details like colors, font, formula etc.

How SyncSheet *Works* ?

Step 1&nbsp;
Install SyncSheet add-in from the&nbsp;<a href="https://appsource.microsoft.com/en-us/product/office/WA200005223?tab=Overview" target="_blank" rel="noopener">Microsoft Store</a> and complete the login process to get started.
&nbsp;
Step 2
Create a sync rule for your requirement by specifying the Excel sheet to sync with its corresponding Google Sheet.
&nbsp;
Step 3
Click on pull/push button to start the data syncing.

SyncSheet allows you to efficiently synchronize data between Google Sheet and Excel Sheet. Sync cell values, background colors, font colors, cell formulas, font size and other supported formattings.

Top *Features* of SyncSheet

Sync data from an Excel sheet &rarr; Google Sheet. You have the option to push all sheets at once, a single sheet, or the selected sheet.

Push Data  ⏫

Retrieve data from a Google Sheet &rarr; Excel Sheet with a simple button click. Similar to the push, you can fetch all sheets at once or choose to fetch only a specific sheet.

Pull Data ⏬

You can easily sync data between a single Excel Sheet and multiple Google Sheets. This helps you put together all the data you need in Excel from different places on Google Sheets, making it simpler for you to work with everything at one place.

Sync with Multiple Sheets

SyncSheet preserves the format and formulas during synchronization. It can sync formattings like background colors, font styles, font colors, formulas, borders etc.

Rich Data Format Support 😎

If you ever need to sync only a specific range that is even possible with different range options like ALL_DATA_RANGE, CURRENTLY_DELECTED_RANGE.

Sync Specific Range 🔀

SyncSheet is a tool which essentially syncs data from MS Excel to Google Spreadsheet and vice-versa

SyncSheet

Enable additional features in Google Chat like Quote, Audio Recorder, Encrypt and Decrypt Message, Copy Link, Reminder, Text Transform, Code and Code Block, Color Preview.

How to Install *Chat Plus* ?

Step-1
Install <a href="https://chromewebstore.google.com/detail/chat-plus-for-google-chat/njkkenehdklkfdkmonkagaicllmnfcda?hl=en" target="_blank" rel="noopener">Chat Plus</a> Chrome Extension from the Webstore.
&nbsp;
Step-2
After installation, navigate to Google Chat and you will notice additional features with "cp" icons. In case you can't locate them, just refresh the page.
&nbsp;
Step-3
Go through one-time login with Google to get started.

Top *Features* of Chat Plus

Use the audio recorder to record your message, and it will automatically convert it into a URL. You can either download it as an mp3 or share it in a message right away.

Send Audio Message

Share either your current location or any arbitrary location in Google Chat. The shared location comes with a link to Google Map.

Location Sharing

Secure your confidential message by encrypting it with a password, protecting it from prying eyes.

Encrypt and Decrypt Message

Bookmark your important messages and save them to your bookmark list for quick reference at a later time.

Set a Bookmark

Schedule reminders for your important messages to receive timely notifications. Assists in ensuring you never miss your weekly stand-up meeting again.

Set a Reminder

Schedule a message to be sent at a later time. This helps address timezone challenges and ensures messages are delivered when team members are active and are more likely to see and respond them.

Schedule a Message

<ol>
<li>Code and Code Block: Send your code pre-formatted to maintain its structure and readability.</li>
<li>Color And Audio Preview: Get a quick preview of the Hex Colors and Audio links present in the chat.</li>
<li>Text Transform: Convert your lengthy messages that you've typed into lowercase, UPPERCASE, Sentence case, Capitalize and toGGle Case.</li>
<li>Quote Text: Quote either a selected part of a message or the entire message.</li>
</ol>

More features

Make your Google Chat experience more exciting with Chat Plus

Chat Plus

Create custom fields in Google Calendar™ events to include additional information. Select the display location according to your preferences. Generate surveys and collect feedback from event attendees.

How *Custom Fields* Works?

Step-1
Install the <a href="https://chromewebstore.google.com/detail/custom-fields-for-google/fgomfahnlnkadfcbnlbfddbdfhckbamm?hl=en" target="_blank" rel="noopener">Custom Fields</a> add-on from Chrome Web Store.
&nbsp;
Step-2
Upon installation, you will notice additional buttons enabled on Google Calendar event details page. Please watch the video for a detailed walkthrough.
&nbsp;
Step-3
For the initial setup, you'll need to log in to your Google account to get started.

The Custom Fields for Google Calendar&trade; extension allows event organizers to create personalized fields that can be used to include additional information not present in a standard event details page. These custom fields offer a useful and straightforward way to provide more context and details about an event.

Top *Features*

Event organizers can create fields with various types such as text, checkboxes, radio buttons, color pickers, dropdown, and more to put additional information. Whether it's about meeting protocols, food choices, opinions, or surveys, these fields help organizers connect with their guests and collect feedback.

Field Types Supported

The "Survey" option enables guests to actively provide their responses on a field. Organizers can then view and analyze all guest's feedback/surveys related to the event.

Collect Attendee Responses

All fields adhere to guest permissions, allowing only those with modification access to edit a field.

Roles Based Access

Easily edit custom fields with our inline editor which includes a convenient quick preview. The ability to see a live representation of how the field will appear on the actual page provides a great flexibility.

Quick Inline Editing

You can seamlessly access the fields on both the event details page and the calendar view, providing a native experience. You will feel like the fields are part of Google Calendar interface.

SITEMAP

Index of posts and products published on Tabgraf

Posts

Products

Quick Links

Curiosity