Tag Archives: sheets

Evolving automations into applications using Apps Script

Posted by Wesley Chun (@wescpy), Developer Advocate, Google Cloud

Editor’s Note: Guest authors Diego Moreno and Sophia Deng (@sophdeng) are from Gigster, a firm that builds dynamic teams made of top global talent who create industry-changing custom software.

Prelude: Data input & management … three general choices

Google Cloud provides multiple services for gathering and managing data. Google Forms paired with Google Sheets are quite popular as they require no engineering resources while being incredibly powerful, providing storage of up to 5 million rows of data and built-in analytics for small team projects.

At the other end of the spectrum, to support a high volume of users or data, Google Cloud provides advanced serverless platforms like Google App Engine (web app-hosting) and Google Cloud Functions (function/service-hosting) that can use Google Cloud Firestore for fast and scalable data storage. These are perfect for professional engineering teams that need autoscaling to respond to any level of user traffic and data input. Such apps can also be packaged into a container and deployed serverlessly on Google Cloud Run.

However, it's quite possible your needs are right in-between. Today, we're happy to present the Gigster story and their innovative use of Google Apps Script—a highly-accessible service conventionally relegated to simple macro and add-on development, but which Gigster used to its advantage, building robust systems to transform their internal operations. Apps Script is also serverless, meaning Gigster didn't have to manage any servers for their application nor did they need to find a place to host its source code.

The Gigster story

Gigster enables distributed teams of software engineers, product managers and designers to build software applications for enterprise clients. Over the past five years, Gigster has delivered thousands of projects, all with distributed software teams. Our group, the Gigster Staffing Operations Team, is responsible for assembling these teams from Gigster’s network of over 1,000 freelancers.

Two years ago, our team began building custom software to automate the multi-stage and highly manual team staffing process. Building internal software has allowed the same-size Staffing Operations Team (3 members!) to enjoy a 60x reduction in time spent staffing each role.

The Apps Script ecosystem has emerged as the most critical component in our toolkit for building this internal software, due to its versatility and ease of deployment. We want to share how one piece of the staffing process has evolved to become more powerful over time thanks to Apps Script. Ultimately, we hope that sharing this journey enables all types of teams to build their own tools and unlock new possibilities.

End-to-end automation in Google Sheets

Staffing is an operationally intensive procedure. Just finding willing and able candidates requires numerous steps:

  1. Gathering and formatting customer requirements.
  2. Communicating with candidates through multiple channels.
  3. Logging candidate responses.
  4. Processing paperwork for placement

To add complexity, many of these steps require working with different third-party applications. For awhile, we performed every step manually, tracking every piece of data generated in one central Sheet (the “Staffing Broadcast Google Sheet”). At a certain point, this back-and-forth work to log data from numerous applications became unsustainable. Although we leveraged Google Sheets features like Data Validation rules and filters, the Staffing Broadcast Sheet could not alleviate the high degree of manual processes that were required of the team.

centralized Staffing Broadcast Google Sheet

The centralized Staffing Broadcast Google Sheet provided organization, but required a high degree of manual entry for tracking candidate decisions.

The key transformation was integrating Sheets data with third-party APIs via Apps Script. This enabled us to cut out the most time-consuming operations. We no longer had to flip between applications to message candidates, wait for their replies, and then manually track responses.

To interact with these APIs, we built a user interface directly into the Staffing Broadcast Google Sheet. By introducing an information module, as well as drop-down lists and buttons, we were able to define a small set of manual actions versus the much wider list of tasks the tool would perform automatically across multiple applications.

integrating Apps Script with third-party APIs

By integrating Google Apps Script with third-party APIs and creating a user interface, we evolved the Staffing Broadcast Tool to centralize and automate almost every step of the staffing process.

doPost() is the key function in our staffing tool that facilitates third-party services triggering our Apps Script automations. Below is a snippet of how we listened to candidates' responses from a third-party messaging application. In this case, queueing the third-party message in a Google Sheet so it can be processed with improved error-handling.

/**
* Receive POST requests and record to queue.
*/
doPost(e) {
var payload = e.postData.contents;
SpreadsheetApp.openById(SPREADSHEET_ID)
.getSheetByName("Unprocessed")
.appendRow([payload]);
return ContentService.createTextOutput(""); // Return 200
}

Almost all manual work associated with finding candidates was automated through the combination of integrations with third-party APIs and having a user interface to perform a small, defined set of actions. Our team’s day-to-day became shockingly simple: select candidates to receive messages within the Staffing Broadcast Tool, then click the “Send Broadcast” button. That’s it. The tool handled everything else.

Leveraging Sheets as our foundation, we fundamentally transformed our spreadsheet into a custom software application. The spreadsheet went from a partially automated datastore to a tool that provided an end-to-end automated solution, requiring only the click of a few buttons to execute.

Evolution into a standalone application

While satisfied, we understood that having our application live in Google Sheets had its limitations, namely, it was difficult for multiple team members to simultaneously use the tool. Using doGet(), the sibling to doPost(), we began building an HTML frontend to the Staffing Broadcast Tool. In addition to resolving difficulties related to multiple users being in a spreadsheet, it also allowed us to build an easier-to-use and more responsive tool by leveraging Bootstrap & jQuery.

Having multiple users in a single Google Sheet can create conflicts, but Apps Script allowed us to build a responsive web app leveraging common libraries like Bootstrap & jQuery that eliminated those problems while providing an improved user experience.

When other teams at Gigster got wind of what we built, it was easy to grant access to others beyond the Staffing Operations Team. Since Apps Script is part of the G Suite developer ecosystem, we relied on Google’s security policies to help deploy our tools to larger audiences.

While this can be done through Google’s conventional sharing tools, it can also be done with built-in Apps Script functions like Session.getActiveUser() that allow us to restrict access to specific Google users. In our case, those within our organization plus a few select users.

To this day, we continue to use this third version of the Staffing Broadcast Tool in our daily operations as it supports 100% of all client projects at Gigster.

Conclusion

By fundamentally transforming the Staffing Broadcast Tool with Apps Script, Gigster’s Staffing Operations Team increased its efficiency while supporting the growth of our company. Inspired by these business benefits, we applied this application-building approach using Apps Script for multiple tools, including candidate searching, new user onboarding, and countless automations.

Our team’s psychological shift about how we view what we are capable of, especially as a non-engineering team, has been the most valuable part of this journey. By leveraging an already familiar ecosystem to build our own software, we have freed team members to become more self-sufficient and valuable to our customers.

To get started on your Apps Script journey, we recommend you check out the Apps Script Fundamentals playlist and the official documentation. And if you're a freelancer looking to build software applications for clients, we’re always looking for talented software engineers, product managers or designers to join Gigster’s Talent Network.

Thank you to Sandrine Bitton, the third member of the Staffing Operations Team, for all her help in the development of the Staffing Broadcast Tool.

Version control to Major Tom—keeping track of work in G Suite

How many times have you worked in a document only to realize halfway through that you’re editing an outdated version? Beyond being frustrating, this can cause confusion and slow down your project. One of the great things about working in cloud-native tools like G Suite is that everything is automatically saved. You never have to worry if you’re working on the most up-to-date version of a document because your apps do that for you.

But for the times that you need to go back and see changes, it’s simple. Go to File > Version history > See version history and view a complete list of changes to your document in one place. You can restore previous versions of a document there, too.

Another trick is to simply click the text at the top of your navigation bar that says “last edit was…” or “all changes saved” in Google Docs. If you hover over that text, it will actually tell you what the last change to the document was and who made it. Or if you’re returning to a document, you can click “See new changes” and the Doc will highlight edits that have been made since your last visit.

Read on for more tips on how to keep track of work in G Suite.

Take it a step further

Beyond viewing versions, there are other cool things you can do to keep track of work in G Suite. You can:

1. Make copies of specific versions

This is great for sharing “before” and “after” versions of your work with your boss. You can make copies of specific versions within version history. Click the three dot icon next to your selected version and choose “make a copy” to have a second record.

make copies.gif

2. Name specific versions of your work. 

In version history, you can keep track of your work by naming individual versions in your list. Because all of your changes are saved in the cloud, it’s easy to go back and change version names instead of having ten different versions of a “Final” document floating in email threads. Bonus points for creative names!

name specific versions.gif

3. See who’s viewed your work and when. 

If you’ve ever wondered who specifically has looked at your work, there’s a way for you to view activity within Google Docs, Sheets or Slides. Those folks who use paid versions can go to Tools > Activity dashboard and see detail about the number of people reviewing their document, who specifically has reviewed and viewership trends. If you want to get there quicker, you can use this keyboard shortcut to see the activity dashboard: Ctrl + Alt/Option + T, then click Z.

4. Set up notifications to see when changes are made in Sheets. 

If you’re working in a spreadsheet to track a project, you may want to see regular updates for when changes are made. You can set up notifications to alert you of changes immediately after someone changes data in a cell. Select Tools > Notification rules and choose the settings you prefer.

notifications.gif

Don’t let version issues slow down your work. Use these tips to maneuver past mix-ups.

Version control to Major Tom—keeping track of work in G Suite

How many times have you worked in a document only to realize halfway through that you’re editing an outdated version? Beyond being frustrating, this can cause confusion and slow down your project. One of the great things about working in cloud-native tools like G Suite is that everything is automatically saved. You never have to worry if you’re working on the most up-to-date version of a document because your apps do that for you.

But for the times that you need to go back and see changes, it’s simple. Go to File > Version history > See version history and view a complete list of changes to your document in one place. You can restore previous versions of a document there, too.

Another trick is to simply click the text at the top of your navigation bar that says “last edit was…” or “all changes saved” in Google Docs. If you hover over that text, it will actually tell you what the last change to the document was and who made it. Or if you’re returning to a document, you can click “See new changes” and the Doc will highlight edits that have been made since your last visit.

Read on for more tips on how to keep track of work in G Suite.

Take it a step further

Beyond viewing versions, there are other cool things you can do to keep track of work in G Suite. You can:

1. Make copies of specific versions

This is great for sharing “before” and “after” versions of your work with your boss. You can make copies of specific versions within version history. Click the three dot icon next to your selected version and choose “make a copy” to have a second record.

make copies.gif

2. Name specific versions of your work. 

In version history, you can keep track of your work by naming individual versions in your list. Because all of your changes are saved in the cloud, it’s easy to go back and change version names instead of having ten different versions of a “Final” document floating in email threads. Bonus points for creative names!

name specific versions.gif

3. See who’s viewed your work and when. 

If you’ve ever wondered who specifically has looked at your work, there’s a way for you to view activity within Google Docs, Sheets or Slides. Those folks who use paid versions can go to Tools > Activity dashboard and see detail about the number of people reviewing their document, who specifically has reviewed and viewership trends. If you want to get there quicker, you can use this keyboard shortcut to see the activity dashboard: Ctrl + Alt/Option + T, then click Z.

4. Set up notifications to see when changes are made in Sheets. 

If you’re working in a spreadsheet to track a project, you may want to see regular updates for when changes are made. You can set up notifications to alert you of changes immediately after someone changes data in a cell. Select Tools > Notification rules and choose the settings you prefer.

notifications.gif

Don’t let version issues slow down your work. Use these tips to maneuver past mix-ups.

Source: Drive


ICYMI in May: here’s what happened in G Suite

While Laurel vs. Yanny dominated office chatter this past month, businesses also got their first taste of working in the all-new Gmail. Packed with advanced security features, more integrations and new applications of Google’s artificial intelligence, Gmail is already receiving positive feedback like this, this and this from users.

To experiment with the new Gmail in your personal Gmail accounts, go to Settings in the top right of your inbox and select “Try the new Gmail.” It’s also available for G Suite businesses to use in the Early Adopter Program (EAP).

Subhead 1 ICYMI G Suite

We all have that one coworker who we rely on for tips on how to master spreadsheets, draft functions, organize email and more. We want to empower anyone who uses G Suite to become the go-to “pro” in the office. To help, we’re kicking off a bimonthly series called “G Suite Pro Tips.”

This month, a Google developer expert breaks down how to combine data from one spreadsheet into another spreadsheet in less than two minutes:

Import data from one spreadsheet to another
Subhead 2 ICYMI G Suite

→ Now you can work offline with ease in the new Gmail. Open Gmail in Chrome browser and search, write, delete or archive up to three months worth of messages.

→ We’ve made it easier for IT admins to export and download a copy of data securely from core services like Gmail, Google Docsand more. Navigate to the Tools section in the right-side slideout menu of the Admin console and select “Data Export.”

→ We made some minor changes to Drive’s user interface (UI), stuff like colors and fonts.

Drive UI change

→ We're rolling out Hangouts Meet to new regions. Welcome Colombia, Croatia, Lithuania and Portugal! We’re also automatically enabling Hangouts Meet (instead of Classic Hangouts) for all G Suite domains. Now, you’ll see Meet video meeting details in your Google Calendar events.

→ We’ve expanded Braille support for Sheets on Windows computers. Now you can use Braille on ChromeOS, Windows and Mac systems. Learn more.

Jamboard is now Bluetooth-enabled. Connect Bluetooth Conferencing devices (speaker and microphone combined) for your next jam session.

Check out a full recap of all product updates in May (PDF version).

Source: Drive


ICYMI in May: here’s what happened in G Suite

While Laurel vs. Yanny dominated office chatter this past month, businesses also got their first taste of working in the all-new Gmail. Packed with advanced security features, more integrations and new applications of Google’s artificial intelligence, Gmail is already receiving positive feedback like this, this and this from users.

To experiment with the new Gmail in your personal Gmail accounts, go to Settings in the top right of your inbox and select “Try the new Gmail.” It’s also available for G Suite businesses to use in the Early Adopter Program (EAP).

Subhead 1 ICYMI G Suite

We all have that one coworker who we rely on for tips on how to master spreadsheets, draft functions, organize email and more. We want to empower anyone who uses G Suite to become the go-to “pro” in the office. To help, we’re kicking off a bimonthly series called “G Suite Pro Tips.”

This month, a Google developer expert breaks down how to combine data from one spreadsheet into another spreadsheet in less than two minutes:

Import data from one spreadsheet to another
Subhead 2 ICYMI G Suite

→ Now you can work offline with ease in the new Gmail. Open Gmail in Chrome browser and search, write, delete or archive up to three months worth of messages.

→ We’ve made it easier for IT admins to export and download a copy of data securely from core services like Gmail, Google Docsand more. Navigate to the Tools section in the right-side slideout menu of the Admin console and select “Data Export.”

→ We made some minor changes to Drive’s user interface (UI), stuff like colors and fonts.

Drive UI change

→ We're rolling out Hangouts Meet to new regions. Welcome Colombia, Croatia, Lithuania and Portugal! We’re also automatically enabling Hangouts Meet (instead of Classic Hangouts) for all G Suite domains. Now, you’ll see Meet video meeting details in your Google Calendar events.

→ We’ve expanded Braille support for Sheets on Windows computers. Now you can use Braille on ChromeOS, Windows and Mac systems. Learn more.

Jamboard is now Bluetooth-enabled. Connect Bluetooth Conferencing devices (speaker and microphone combined) for your next jam session.


Check out a full recap of all product updates in May (PDF version).

Source: Drive


G Suite Pro Tips: how to sync one spreadsheet to another in Google Sheets

When it comes to analyzing data at work, we often have to pull information from several different sources in order to see the big picture. But if you need to manage product inventory or report on quarterly sales figures, spending time flipping between spreadsheets or manually copying and pasting data into one place can take up too much time. There’s a better option.

With G Suite—Google’s suite of cloud-based collaboration and productivity apps—there are a ton of ways you can skip repetitive tasks, including in Google Sheets.

Today, we’re kicking off a tips series to help you become a G Suite pro—starting with how to combine data from one spreadsheet into another. Check it out.

G Suite Pro Tips: syncing one spreadsheet to another

Combining data from two Google Sheets in four steps

With Sheets, it’s easy to combine data into one spreadsheet to create a single source of truth.

Step 1: Identify the spreadsheets you want to combine.

Pull up the two spreadsheets that you’d like to import data between. You should have the original spreadsheet (Ex: “Sales Revenue”) and the one you want to add information into (Ex: “Product Inventory”).

Step 2: Grab two things from the original sheet.

You need two pieces of information from the original spreadsheet in order to move the data: the spreadsheet URL and the range of cells where you want to pull the data from. In this example, our original spreadsheet’s name is “Sales Revenue.”

First, highlight and copy the full spreadsheet URL from the original spreadsheet (Note: you can also use the spreadsheet “key,” which is a code hidden inside the URL between the “d/” and “/edit.” It looks like a jumbled mix of letters and numbers.).

G Suite Pro Tips: image 1

Next, before you switch to the new spreadsheet, make sure to note the range of cells where you want to pull the data from in the original spreadsheet. For example, A:1 to C:10.

Step 3: Use a Google Sheets function to port your data over.

Now we use the IMPORTRANGE function. First, click into the new spreadsheet where you’d like to add data into. In this example, it’s named “Product Inventory.” Insert columns or rows into the spreadsheet where you want to put data.

Next, type =IMPORTRANGE in the cell (you can choose to use all caps or not, it doesn’t matter.). The function will then ask you for three things:

  1. The URL of the original spreadsheet (or the spreadsheet key, both options work.)
  2. The name of the specific tab in your spreadsheet that you’re pulling information out of
  3. The range of cells for data you need

It will look similar to this:

=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1RNez4bhTMt_evAdHrFOBHeBgk1l5HAWVTb43EKpYHR8/edit#gid=0,””Sales Revenue by Quarter!A1:C10”)

It’s important to note that you have to use the specific name of the tab in the sheet in the formula. So for this example, the name of the original spreadsheet housing multiple datasets is called “Sales Revenue,” but the name of the specific tab with our data in it is called “Sales Revenue by Quarter.” We want to use the specific tab’s name to avoid our function breaking in the future when new sheets or tabs are created.

Oh and another trick: don’t forget to add the exclamation point (!) before the data range. That’s important, too.

Step 4: Import your data.

After you’ve added your IMPORTRANGE formula, you can click enter.

If it’s the first time you’ve imported data from that particular spreadsheet, a pop-up might appear. Don’t worry! This security check makes sure you’re okay with granting any collaborators on this spreadsheet access to data that lives in another spreadsheet. It will ask you to “Allow access” when you see the #REF in your cell. Go ahead and click yes.

Voilà! Your data will appear in the new spreadsheet.

Focus on work that’s important

Google Sheets has more than 400 functions you can use to help speed up work. To learn more about how cloud-based tools like Sheets can help businesses uncover insights quicker—and, as a result, encourage employees to spend time on strategic work—check out this post.

Think macro: record actions in Google Sheets to skip repetitive work

Since their debut nearly 40 years ago, spreadsheets have remained core to how businesses get work done. From analyzing quarterly revenue to updating product inventory, spreadsheets are critical to helping companies gather and share data to inform quicker decisions—but what else can you do if they’re in the cloud?

We’ve been focused on making Google Sheets better for businesses for this reason, which is why we’ve recently added new features to help teams analyze and visualize their data. Today we’re adding more updates to Sheets, including a way to record macros in the cloud to automate repetitive tasks, as well as more formatting options. Check it out.

Record macros in Sheets, skip mundane tasks

We want to help companies automate work by approaching macros differently: cloud-first. Starting today, you can record macros in Sheets. Let’s say you need to format new data imports or build the same chart across multiple sheets of quarterly data. Repeating the same steps manually can take hours, but the Sheets macro recorder lets you record those actions and play them back on command without having to write any code.

Here’s how it works: when you record a macro, Sheets converts the macro actions into an Apps Script automatically. If you want to update your macro, you can simply edit the script directly instead of having to re-record the macro from scratch. You can also write your own Apps Script functions and import them as new macros.

The best part about Sheets Macros is that they’re built for use in cloud-based files, which means that teams can run macros at the same time that others are working in the sheet without interrupting them. For example, a finance team having a budget meeting can run macros while reviewing the same spreadsheet. It also means that coworkers or clients won’t be forced to download sensitive files to use your macros. Since your Sheets files are in the cloud, you can keep tighter controls over who can view and re-share your data.

More updates to Sheets

You may have noticed that over the past few years we’ve put effort toward building Sheets features to help businesses view, analyze and share their data more easily. This includes adding new chart types (waterfall and 3D), ways to embed charts while keeping data up-to-date in Docs and Slides (even if you move files), more functions (up to 400+ now), additional formatting, print options and more.

Today, we’re adding even routinely requested features, including the ability to add printing page breaks, custom paper sizes, more options for row and column grouping and a way to add checkboxes in cells. We’ve also made it possible for you to group your data by time frame (like week, month or year) when you create pivot tables.

Speaking of pivot tables, our engineering team has also been hard at work bringing the power of Google’s artificial intelligence into Sheets to help teams know what their data knows. You might have noticed that we recently added intelligent pivot tables in Sheets to help analyze and find new insights, no matter how skilled you are at data analytics.

Pivot tables

Companies like Whirlpool Corporation and Sanmina are using Sheets for more collaborative data analysis. These latest additions are designed to make Sheets a go-to resource for businesses and will roll out over the next few weeks. Learn more about Sheets on our Learning Center.

An easy formula: 5 reasons to use Google Sheets

Import, export, email. Do it again. When it comes to data analysis, it’s easy to fall into routine. But no matter how much of a whiz you are at formulas or pivot tables, superb spreadsheet skills only take you so far if you’re working with multiple versions or outdated datasets.

On average, employees spend up to eight hours each week—an entire work day—searching for and consolidating information. What if businesses spent their time applying data insights instead of tracking them down?

We designed cloud-based tools, like Google Sheets, to make it easier to quickly organize and analyze information in one place. Here are five reasons why you should try using Sheets.

1. Your data always stays up to date.

Image 1 - Sheets

Working in the cloud means your data can easily stay up to date because information is automatically saved as it’s typed. Multiple team members can collaborate in real-time from their phone, tablet or computer (online and offline) and create a single source of truth for projects, like quarterly budgets.

Need personalization? No problem. You can look at the same data as your coworkers without disrupting their viewsort information to be in a specific order or hide sheets that you don’t need to see. If you’re worried about others mucking up your data, you can protect cells by setting custom share settings. Plus, you don’t have to worry about version control. You can see changes or revert to previous versions in File > Version History or by clicking on “All Changes Saved in Drive” at the top of your Sheet.

2. You can use Google’s artificial intelligence to speed up analysis.

Image 2 - Sheets

Powered by Google’s machine intelligence, Sheets does a lot of the heavy lifting for you when it comes to data analysis. You can ask a question about your data and Sheets will return an answer using natural language processing. Sheets also builds chartssuggests formulas and creates pivot tables for you.

Data gurus who want to take analysis a step further can sync Sheets to BigQuery to analyze information against large data sets. Check out this example or this example.

3. You can help keep your data secure.  

We built security features across G Suite to help you manage your most sensitive business information. Sheets is no exception.

Clicking “Share” in the upper righthand corner of your Sheet determines access settings for individual team members, so you stay in control of who can view, edit or comment on your documents. Additional IRM (Information Rights Management) controls allow you to easily disable options to download, copy or print spreadsheets, and you can set expiration dates if you need to suspend access to confidential information. Admins also have the power to restrict file sharing within an organization or to whitelist domains.

4. You can work the way you’re used to working.

We get it—you need tools that won’t interrupt work processes (especially if you’ve used those processes forever). That’s why we’ve baked in functionality that teams are used to using.

  • Let’s start with functions. You can use hundreds of functions that you’re used to working with, like VLOOKUP, SUMIFS and IPMT. We’ve also created unique-to-Sheets functions to help you crunch numbers in new ways, like: FILTER to quickly organize a spreadsheet according to an input, QUERY to pull specific results out of a central dataset or GOOGLEFINANCE to source data directly from Google Finance. Outside of functions, can also create pivot tables to analyze your data (or let Sheets do it for you.).
  • What about organization? Color cells using Conditional Formatting, create drop-down lists with Data Validation or freeze headers at the top of your sheet, to make your data presentation-friendly. Once your data is organized, you can visualize it in pie charts, bar charts, line charts—charts galore.
  • Editing on the fly. Sheets is available online and offline—and on desktop web browsers, and Android and iOS apps—which means your teams don’t have to stop working during business travel or when Wi-Fi is down.

5. You can automate processes.

When information is in one place, it’s easier to automate processes. Let’s say you need to order supplies for your office. Instead of emailing back-and-forth to coordinate purchases you can use G Suite apps instead.

NYT Sheets GIF

For example, say you’re an inventory manager at a retail company and you gather supply requests from your teams using Google Forms. You can import the Form responses into a Sheet  to keep a record of needs. If your Sheet is synced to Gmail with a script, it can send emails to relevant parties about requests automatically, so they can see and approve requests in one spot—the Sheet. Poof! No more unnecessary email clutter.

There are a ton of ways you can automate mundane tasks or speed up workflows. Check out how The New York Times used the Sheets API to report congressional votes quickly.

Let data drive your business decisions. Learn how you can access insights using Sheets or visit the G Suite Learning Center for specific product tips.

Pivot to the cloud: intelligent features in Google Sheets help businesses uncover insights

When it comes to data in spreadsheets, deciphering meaningful insights can be a challenge whether you’re a spreadsheet guru or data analytics pro. But thanks to advances in the cloud and artificial intelligence, you can instantly uncover insights and empower everyone in your organization—not just those with technical or analytics backgrounds—to make more informed decisions.

We launched "Explore" in Sheets to help you decipher your data easily using the power of machine intelligence, and since then we’ve added even more ways for you to intelligently visualize and share your company data. Today, we’re announcing additional features to Google Sheets to help businesses make better use of their data, from pivot tables and formula suggestions powered by machine intelligence, to even more flexible ways to help you analyze your data.

Easier pivot tables, faster insights

Many teams rely on pivot tables to summarize massive data sets and find useful patterns, but creating them manually can be tricky. Now, if you have data organized in a spreadsheet, Sheets can intelligently suggest a pivot table for you.


In the Explore panel, you can also ask questions of your data using everyday language (via natural language processing) and have the answer returned as a pivot table. For example, type “what is the sum of revenue by salesperson?” or “how much revenue does each product category generate?” and Sheets can help you find the right pivot table analysis.

GIF

In addition, if you want to create a pivot table from scratch, Sheets can suggest a number of relevant tables in the pivot table editor to help you summarize your data faster.

Suggested formulas, quicker answers

We often use basic spreadsheet formulas like =SUM or =AVERAGE for data analysis, but it takes time to make sure all inputs are written correctly. Soon, you may notice suggestions pop up when you type “=” in a cell. Using machine intelligence, Sheets provides full formula suggestions to you based on contextual clues from your spreadsheet data. We designed this to help teams save time and get answers more intuitively.

Formula suggestions in Sheets

Even more Sheets features

We’re also adding more features to make Sheets even better for data analysis:

  • Check out a refreshed UI for pivot tables in Sheets, and new, customizable headings for rows and columns.
  • View your data differently with new pivot table features. When you create a pivot table, you can “show values as a % of totals” to see summarized values as a fraction of grand totals. Once you have a table, you can right-click on a cell to “view details” or even combine pivot table groups to aggregate data the way you need it. We’re also adding new format options, like repeated row labels, to give you more fine-tuned control of how to present your summarized data.
  • Create and edit waterfall charts. Waterfall charts are good for visualizing sequential changes in data, like if you want to see the incremental breakdown of last year’s revenue month-by-month. Select Insert > Chart > Chart type picker and then choose “waterfall.”
  • Quickly import or paste fixed-width formatted data files. Sheets will automatically split up the data into columns for you without needing a delimiter, like commas, between data.

These new Sheets features will roll out in the coming weeks—see specific details here. To learn more about how G Suite can help your business uncover valuable insights and speed up efficiencies, visit the G Suite website. Or check out these tips to help you get started with Sheets.

Source: Google Cloud


Pivot to the cloud: intelligent features in Google Sheets help businesses uncover insights

When it comes to data in spreadsheets, deciphering meaningful insights can be a challenge whether you’re a spreadsheet guru or data analytics pro. But thanks to advances in the cloud and artificial intelligence, you can instantly uncover insights and empower everyone in your organization—not just those with technical or analytics backgrounds—to make more informed decisions.

We launched "Explore" in Sheets to help you decipher your data easily using the power of machine intelligence, and since then we’ve added even more ways for you to intelligently visualize and share your company data. Today, we’re announcing additional features to Google Sheets to help businesses make better use of their data, from pivot tables and formula suggestions powered by machine intelligence, to even more flexible ways to help you analyze your data.

Easier pivot tables, faster insights

Many teams rely on pivot tables to summarize massive data sets and find useful patterns, but creating them manually can be tricky. Now, if you have data organized in a spreadsheet, Sheets can intelligently suggest a pivot table for you.


In the Explore panel, you can also ask questions of your data using everyday language (via natural language processing) and have the answer returned as a pivot table. For example, type “what is the sum of revenue by salesperson?” or “how much revenue does each product category generate?” and Sheets can help you find the right pivot table analysis.

GIF

In addition, if you want to create a pivot table from scratch, Sheets can suggest a number of relevant tables in the pivot table editor to help you summarize your data faster.

Suggested formulas, quicker answers

We often use basic spreadsheet formulas like =SUM or =AVERAGE for data analysis, but it takes time to make sure all inputs are written correctly. Soon, you may notice suggestions pop up when you type “=” in a cell. Using machine intelligence, Sheets provides full formula suggestions to you based on contextual clues from your spreadsheet data. We designed this to help teams save time and get answers more intuitively.

Formula suggestions in Sheets

Even more Sheets features

We’re also adding more features to make Sheets even better for data analysis:

  • Check out a refreshed UI for pivot tables in Sheets, and new, customizable headings for rows and columns.
  • View your data differently with new pivot table features. When you create a pivot table, you can “show values as a % of totals” to see summarized values as a fraction of grand totals. Once you have a table, you can right-click on a cell to “view details” or even combine pivot table groups to aggregate data the way you need it. We’re also adding new format options, like repeated row labels, to give you more fine-tuned control of how to present your summarized data.
  • Create and edit waterfall charts. Waterfall charts are good for visualizing sequential changes in data, like if you want to see the incremental breakdown of last year’s revenue month-by-month. Select Insert > Chart > Chart type picker and then choose “waterfall.”
  • Quickly import or paste fixed-width formatted data files. Sheets will automatically split up the data into columns for you without needing a delimiter, like commas, between data.

These new Sheets features will roll out in the coming weeks—see specific details here. To learn more about how G Suite can help your business uncover valuable insights and speed up efficiencies, visit the G Suite website. Or check out these tips to help you get started with Sheets.