<p>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.</p>
<p><img title="Conditional Formatting with Icons in MS Excel" src="/uploads/2023/11/conditional-formatting-icons-excel.png" alt="Conditional Formatting with Icons in MS Excel"></p>
<blockquote>
<p>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</p>
</blockquote>
<h2>How it Works</h2>
<p>The image formula helps to render an image form a url which plays the major role rendering the icon.</p>
<pre class="language-javascript"><code>=IF(logical_expression, value_if_true, value_if_false)
=IMAGE(url, [mode], [height], [width]) </code></pre>
<p>You can upload the image to any 3rd party server or cloud and just use the url in the formula</p>
<pre class="language-javascript"><code>=IF(A3&lt;50,IMAGE("your_icon_url",3),IMAGE("your_icon_url",3)) </code></pre>
<p>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;</p>
<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>
<p>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.</p>
<h2>Change Icon Size</h2>
<p>The =IMAGE("your_icon_url", 2) formula accepts a 2nd parameter which sets the size mode.</p>
<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>
<p><img title="Image Formula with Size Parameter" src="/uploads/2023/11/image-formula.png" alt="Image Formula with Size Parameter"></p>
<h2>Put the Icon and Value in the Same Cell</h2>
<p>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.<br><img title="Same Cell Icons" src="/uploads/2023/11/same-cell-icons.png" alt="Same Cell Icons"></p>
<h2>Formatting with SPARKILINE</h2>
<p>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.</p>
<pre class="language-javascript"><code>=SPARKLINE(E2,{"charttype","bar";"max",100})</code></pre>
<p><img title="Sparkline Chart" src="/uploads/2023/11/sparkline.png" alt="Sparkline Chart"></p>
<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>
<p>Chat Plus is a chrome extension developed by Tabgraf that enables additional features in Google Chat some of which are:-</p>
<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>
<p>&nbsp;</p>
<h1>Send Audio message in Google Chat</h1>
<p>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.</p>
<p><img src="/uploads/2023/10/chat-plus-audio-recorder.png"></p>
<p>&nbsp;</p>
<p>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.</p>
<p><img src="/uploads/2023/10/chat-plus-audio-screen.png"></p>
<p>&nbsp;</p>
<h1>How the Audio messaging works ?</h1>
<p>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:</p>
<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>
<p>&nbsp;</p>
<h1>How the audio chunks is converted back to audio&nbsp;?</h1>
<p>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.</p>
<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>
<p>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).</p>
<pre class="language-javascript"><code>const buffer = Buffer.from(base64AudioMessage , "base64");

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

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?

<p>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.</p>
<p>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.</p>
<p>Listed below are the key features of Timesheet Tracker<em><strong>.</strong></em></p>
<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>
<p><img src="/uploads/2023/11/timesheet-tracker.png" alt="Timesheet Tracker"></p>
<h2>How to Install</h2>
<p>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.</p>
<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>
<p><img src="/uploads/2023/11/projects-timesheet-tracker.png" alt="Project list - Timesheet Tracker"></p>
<blockquote>
<p>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.</p>
</blockquote>
<h2>Technologies Used</h2>
<p>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.</p>
<ol>
<li>Vue.js and Bootstrap serves the front-end</li>
<li>Google Apps Script handles the backend</li>
</ol>
<p>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.</p>
<p>Here's a basic example of how <code>google.script.run</code> is used with Google Apps Script:</p>
<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>
<p>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.</p>
<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>
<p>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.</p>
<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.

Google Spreadsheet

Tech Tips, Tutorials & How-to Guides

POSTS

How to Apply Conditional Formatting with Icons in Google Sheets

How to send audio message in Google Chat?

How to Create a Timesheet Tracker in Google Spreadsheet

BROWSE ALL TABGRAF TOPICS

Even Guinea Pig Likes
Fresh Carrots🥕

Quick Links

Curiosity

Google Spreadsheet

Tech Tips, Tutorials & How-to Guides

POSTS

How to Apply Conditional Formatting with Icons in Google Sheets

How to send audio message in Google Chat?

How to Create a Timesheet Tracker in Google Spreadsheet

BROWSE ALL TABGRAF TOPICS

Even Guinea Pig LikesFresh Carrots🥕

Quick Links

Curiosity

Even Guinea Pig Likes
Fresh Carrots🥕