Tag Archives: Google Sheets

New sharing dialog for Google Drive, Docs, Sheets, Slides, and Forms

What’s changing 

We’re updating the interface you use to share files from Google Drive, Docs, Sheets, Slides, and Forms on the web. This will replace the previous interface used to share files and manage members of shared drives. These changes will make it easier to share files only with specific people without expanding access beyond what’s needed.

Who’s impacted 

End users

Why it matters 

Sharing files is critical to collaboration. This is especially true now, as more workforces are remote and collaborating on files from different locations. By making it easier to share files with specific people, we hope to improve collaboration while reducing the risk of access by unwanted users. 

Additional details 

We’ve made several changes to the sharing experience. These make it easier to perform common tasks, avoid accidental permission changes, and quickly see who has access to a file. Specifically you may notice:

  • Separated, task-focused interface: The new sharing dialog highlights essential user tasks like sharing a file, changing permissions, and viewing file access. The redesign also visually separates sharing with people and groups from link-sharing. 
  • Quick “copy link” button: We’ve added a “copy link” button to make it easier to get the link without changing link permissions. 
  • Easily see current access: The new interface more clearly shows who currently has access to the item, making it easier to quickly audit and change permissions. 


The new sharing interface for Google Drive and Docs editors files 


The old sharing interface for Google Drive and Docs editors files 

Getting started 


  • Admins: This change will take place by default. There is no admin control for this feature. 
  • End users: This feature will be ON by default. Use our Help Center to learn more about how to share Google Drive files

Rollout pace 



Availability 


  • Available to all G Suite and Drive Enterprise customers, as well as users with personal Google Accounts 

Resources 



Roadmap 


Sort and filter cells by text or fill color in Google Sheets

Quick launch summary 

We’ve added two additional parameters to filter and sort cells by in Google Sheets:
  • Text color 
  • Fill (background) color 

Along with sorting by values and conditions, these filters make it easier and faster to find and surface relevant data in Sheets. This feature will be available on mobile and web.

Getting started 

Admins: No admin action required for this feature.

End users: This feature will be available by default. To use this feature, select Filter > Filter by Color and then select “Fill color” or “Text color”. Matching cells will be display at the top of the range. Visit the Help Center to learn more. Use our Help Center to learn more about sorting and filtering data in Sheets.

Filtering by fill color, then text color.

Rollout pace

Availability

  • Available to all G Suite customers and users with personal Google Accounts

Resources


Set custom table ranges for charts in Google Sheets

What’s changing 

We’re improving the way data is suggested and how data is selected when creating a chart in Google Sheets. It’s now easier to locate and select the data you need when creating a dashboard over a dataset with slicers, pivot tables, charts, and more.

Who’s impacted 

End users

Why you’d use it 

When creating reports in Sheets, it’s common to create multiple charts from the same data table, but using different column ranges. Previously, all data ranges on a table would be used when creating a chart. Now, you’ll be able to select which columns to use for the chart axis and series. This allows you to quickly customize your charts so that they display the most relevant data.

Getting started

Admins: There is no admin action required for this feature.

End users: This feature will be available by default. In the chart editor, you can select a column as the X-axis and under “Series” you can select additional columns to populate your chart.


Rollout pace


Availability

  • Available to all G Suite customers and users with personal Google accounts

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.

Update to Sheets API v4 migration timeline

Quick launch summary 

Earlier this year, we announced the retirement of the Sheets v3 API. At that time, we stated that you’d need to migrate any applications built on the v3 API to the v4 API by March 3, 2020 to ensure they continue working properly.

We’re extending our timeline to allow developers additional time to migrate their applications: 
  • Starting on April 9, 2020, the spreadsheet and spreadsheets.readonly scopes can no longer be used to list a user’s spreadsheets in the v3 API. 
  • On September 30, 2020, the remainder of the v3 API will be turned down.

For information on transitioning your apps, check out our migration guide.

Break out a single value within a pie chart in Google Sheets

Quick launch summary 

You can now “pull out” and highlight a slice from a pie or donut chart in Google Sheets. This feature gives you more ways to control the look of your charts and better display the most important data in Sheets.


Getting started 

End users: This feature is available by default. Visit the Help Center article to learn more about using this feature.

Rollout pace


Availability 


  • Available to all G Suite customers 

Resources 


Data Connector for Zendesk now available as an add-on for Google Sheets

What’s changing 

You can now import and analyze data from Zendesk in Google Sheets with a new data connector. This add-on is now available in the G Suite Marketplace.

Who’s impacted 

End users

Why you’d use it 

Many organizations rely on Zendesk for capturing and taking action on support, customer service, and customer engagement. With the Data Connector for Zendesk, you can use it to configure and save valuable query configurations and load corresponding data into Google Sheets for analysis.


With the add-on, you can search against tickets in Zendesk (and return data to Sheets from that search), list tickets and metrics, load previous searches, and refresh query results already in your spreadsheet.

When creating a new search, you can select the fields you’d like to import to Sheets and set query conditions.

How to get started 

Additional details 

This add-on will use the Zendesk API to extract data into Sheets. At the moment, any changes to the data will not be reflected in Zendesk.

Helpful links 

Availability 

Rollout details 


G Suite editions 
  • Available to all G Suite editions 

On/off by default? 
  • This add-on can be whitelisted at the domain level. This add-on can be installed from the G Suite Marketplace.

Stay up to date with G Suite launches

Learn how to organize and analyze data with Google Sheets on Coursera

Quick launch summary 

You can learn how to organize information and analyze data using the Getting Started with Google Sheets course now available on Coursera. This course is ideal for users who are new to Sheets, such as new employees being onboarded to G Suite, or students looking to sharpen their analytical skills. 

This course is free for all users, or you can pay an additional fee for access to additional content, projects, quizzes and a course certificate. The Getting Started with Google Sheets course will be available in most countries, see here for more information about availability.

Visit the Coursera website to access the Getting Started with Google Sheets course. To find other learning opportunities, visit the Coursera Google Cloud Learning Site.

Stay up to date with G Suite launches

Instantly create new docs, events, spreadsheets and more from your browser

Quick launch summary

You can quickly create new files directly from your browser with “.new” domains for several Google products. For example, you can type “Cal.new” into your browser to create a new Google Calendar event.



The current “.new” domains are:

Availability

Rollout details



G Suite editions

  • Available to all G Suite editions


On/off by default?
  • This feature will be available by default.


Stay up to date with G Suite launches

Directly click on chart elements to move and delete them in Google Sheets

What’s changing 

We’re adding new features that give users more options to easily customize their charts in Google Sheets. Now you can click directly on data labels, chart titles or legends and drag to reposition them. Or you can easily delete these elements using the delete or backspace keys.

Deleting chart elements in Google Sheets

Repositioning chart elements in Google Sheets.

Who’s impacted 

End users

Why you’d use it 

These features give you more ways to control the look and feel of your charts. For example, you can now easily reposition data labels that overlap to avoid clutter and ensure that key data points stand out. Or you can reposition the legend inside the chart area in order to maximize chart space.

How to get started 



Additional details 

When clicking on a group of items (like a set of data labels), the entire group will be selected first. If you want to drill down further (for example, to select an individual data label), simply click again on the specific element.

Note that most chart elements can be repositioned and deleted, except those that derive their position from data. So, you can reposition elements like chart titles or legends, but data points will remain fixed.

Also included in this launch is the ability to navigate through chart elements using your keyboard.

  • Tab: move between elements 
  • Enter: select an individual element from a group 
  • Escape: go back from an individual element to a group 
  • Arrow keys: reposition the selected elements 


For those who use a screen reader, chart elements will be verbalized as you navigate through them.

Helpful links 



Availability 

Rollout details


G Suite editions 

  • Available to all G Suite editions 

On/off by default? 

  • These features will be available by default. 


Stay up to date with G Suite launches