Ceacer

Google’s NotebookLM Android and iOS apps are expected to launch on May 20, according to app store listings. The apps are currently available for pre-order. Since its launch in 2023, the AI-based note-taking and research assistant has only been accessible via desktop. Google is now gearing up to make the service available on the go. NotebookLM is designed to help students, professionals, and researchers better understand complex information through features like smart summaries and the ability to ask questions about documents and other materials. The research assistant also lets you generate AI podcasts, called Audio Overviews, to make it easier to digest complex topics. Image Credits:GoogleAccording to screenshots on the app listings, the dedicated apps will allow users to create new notebooks and view the ones they have already created. They can also upload new sources from their device and view the ones they have already uploaded in each of the notebooks. Plus, the apps will allow you to listen to the Audio Overviews you have generated on the go. In addition to mobile, the apps will be available on iPads and tablets, where you’ll have a bigger screen to multitask. You can choose to pre-order the app on the App Store or pre-register for it on Google Play. If you do so, the app will be automatically downloaded onto your phone on May 20. Given that the apps are expected to become available on the first day of Google I/O, the tech giant will likely share more information about them at the annual conference in a few weeks. Techcrunch event Exhibit at TechCrunch Sessions: AI Secure your spot at TC Sessions: AI and show 1,200+ decision-makers what you’ve built — without the big spend. Available through May 9 or while tables last. Exhibit at TechCrunch Sessions: AI Secure your spot at TC Sessions: AI and show 1,200+ decision-makers what you’ve built — without the big spend. Available through May 9 or while tables last. Berkeley, CA | June 5 BOOK NOW

OpenAI says it’ll make changes to the way it updates the AI models that power ChatGPT, following an incident that caused the platform to become overly sycophantic for many users. Last weekend, after OpenAI rolled out a tweaked GPT-4o — the default model powering ChatGPT — users on social media noted that ChatGPT began responding in an overly validating and agreeable way. It quickly became a meme. Users posted screenshots of ChatGPT applauding all sorts of problematic, dangerous decisions and ideas. In a post on X last Sunday, CEO Sam Altman acknowledged the problem and said that OpenAI would work on fixes “ASAP.” On Tuesday, Altman announced the GPT-4o update was being rolled back and that OpenAI was working on “additional fixes” to the model’s personality. The company published a postmortem on Tuesday, and in a blog post Friday, OpenAI expanded on specific adjustments it plans to make to its model deployment process. OpenAI says it plans to introduce an opt-in “alpha phase” for some models that would allow certain ChatGPT users to test the models and give feedback prior to launch. The company also says it’ll include explanations of “known limitations” for future incremental updates to models in ChatGPT, and adjust its safety review process to formally consider “model behavior issues” like personality, deception, reliability, and hallucination (i.e. when a model makes things up) as “launch-blocking” concerns. “Going forward, we’ll proactively communicate about the updates we’re making to the models in ChatGPT, whether ‘subtle’ or not,” wrote OpenAI in the blog post. “Even if these issues aren’t perfectly quantifiable today, we commit to blocking launches based on proxy measurements or qualitative signals, even when metrics like A/B testing look good.” we missed the mark with last week's GPT-4o update. what happened, what we learned, and some things we will do differently in the future: https://t.co/ER1GmRYrIC — Sam Altman (@sama) May 2, 2025 The pledged fixes come as more people turn to ChatGPT for advice. According to one recent survey by lawsuit financer Express Legal Funding, 60% of U.S. adults have used ChatGPT to seek counsel or information. The growing reliance on ChatGPT — and the platform’s enormous user base — raises the stakes when issues like extreme sycophancy emerge, not to mention hallucinations and other technical shortcomings. Techcrunch event Exhibit at TechCrunch Sessions: AI Secure your spot at TC Sessions: AI and show 1,200+ decision-makers what you’ve built — without the big spend. Available through May 9 or while tables last. Exhibit at TechCrunch Sessions: AI Secure your spot at TC Sessions: AI and show 1,200+ decision-makers what you’ve built — without the big spend. Available through May 9 or while tables last. Berkeley, CA | June 5 BOOK NOW As one mitigating step, earlier this week, OpenAI said it would experiment with ways to let users give “real-time feedback” to “directly influence their interactions” with ChatGPT. The company also said it would refine techniques to steer models away from sycophancy, potentially allow people to choose from multiple model personalities in ChatGPT, build additional safety guardrails, and expand evaluations to help identify issues beyond sycophancy. “One of the biggest lessons is fully recognizing how people have started to use ChatGPT for deeply personal advice — something we didn’t see as much even a year ago,” continued OpenAI in its blog post. “At the time, this wasn’t a primary focus, but as AI and society have co-evolved, it’s become clear that we need to treat this use case with great care. It’s now going to be a more meaningful part of our safety work.”

Plenty of startups hit a wall after their first few rounds of funding, having grown too big for venture funds but still in need of cash. For startups specializing in industrial-scale hardware, which includes many climate tech companies, the problem is especially acute because the capital requirements are so large. Infrastructure funds have long filled that gap, but many have been hesitant to dive into climate tech. One firm thinks that spells opportunity, though. Ara Partners recently raised an $800 million infrastructure fund focused on reducing carbon emissions in industrial sectors, which historically have been hard to decarbonize. Ara had initially targeted $500 million, the firm told TechCrunch, but saw strong support from new and existing investors, including pension funds, insurance companies, endowments, foundations, and sovereign wealth funds from around the world. The new fund has already made three investments, including in an Ireland-based household organic waste recycler and a biofuels terminal developer. The fund’s decarbonization strategy focuses on repurposing existing assets for new low-carbon developments. This significant fundraise arrives at a time of political uncertainty over decarbonization in the U.S., but increasing clarity around its economics. Many companies have been able to drive down costs of low- and zero-carbon technologies in recent years, making them cost competitive with existing approaches. Ara, for example, previously invested in Divert through one of its private equity funds. The company donates food that’s still good and, for food that isn’t edible, turns the waste into biogas that can be sold or used to generate electricity and heat on site. Compared with the alternative — sending the waste to a landfill where it generates methane pollution — Divert’s approach makes a lot of sense environmentally and financially. Techcrunch event Exhibit at TechCrunch Sessions: AI Secure your spot at TC Sessions: AI and show 1,200+ decision-makers what you’ve built — without the big spend. Available through May 9 or while tables last. Exhibit at TechCrunch Sessions: AI Secure your spot at TC Sessions: AI and show 1,200+ decision-makers what you’ve built — without the big spend. Available through May 9 or while tables last. Berkeley, CA | June 5 BOOK NOW The investment firm said it will announce its fourth investment under the strategy “shortly.”

Spotify said on Friday that Apple has approved its U.S. app update that will allow users to access pricing information and external payment links. The approval comes days after a U.S. judge ordered Apple to stop charging commissions on purchases through iPhone apps. “In a victory for consumers, artists, creators, and authors, Apple has approved Spotify’s U.S. app update,” Spotify spokesperson Jeanna Moran said in a statement to TechCrunch. “After nearly a decade, this will finally allow us to freely show clear pricing information and links to purchase, fostering transparency and choice for U.S. consumers.” She continued, “We can now give consumers lower prices, more control, and easier access to the Spotify experience. There is more work to do, but today represents a significant milestone for developers and entrepreneurs everywhere who want to build and compete on a more level playing field. It’s the opening act of a new era, and we could not be more ready for the show.” The updated app, version 9.0.40, is rolling out now on the App Store. Spotify is now one of the first major apps to get a new update, opening up purchases. Spotify submitted the update to Apple yesterday, noting that it could tell customers about different subscription options and how much each one costs right within the iPhone app. Users will also be able to link out to purchase or change their Spotify subscription plan on the company’s website, where its transactions won’t be subject to Apple’s 30 percent service charge on in-app payments. Plus, Spotify said it would now be able to tell U.S. customers about promotional offers in the app. Apple said on Wednesday that it will comply with the court’s order, but that it strongly disagrees with the decision and will appeal. US District Judge Yvonne Gonzalez Rogers said on Wednesday that Apple violated an order to reform its App Store. The tech giant was told to change its app store to allow developers to send customers to their websites to make purchases. According to the ruling, Apple not only failed to comply with the order but did so willfully, with the intention of establishing new anticompetitive barriers. Techcrunch event Exhibit at TechCrunch Sessions: AI Secure your spot at TC Sessions: AI and show 1,200+ decision-makers what you’ve built — without the big spend. Available through May 9 or while tables last. Exhibit at TechCrunch Sessions: AI Secure your spot at TC Sessions: AI and show 1,200+ decision-makers what you’ve built — without the big spend. Available through May 9 or while tables last. Berkeley, CA | June 5 BOOK NOW

Airbnb started rolling out an AI-powered customer service bot in the U.S. last month, CEO Brian Chesky said during the firm’s first-quarter conference call on Thursday. Chesky said 50% of Airbnb’s U.S. users are already using the AI bot for customer service, adding that the company plans to roll out the feature to all its users in the country this month. “One thing I’ll say about AI [is that] it is definitely making the customer experience easier […] It has already led to a 15% reduction in people needing to contact live human agents,” he noted during the analyst call. Last year, the company told TechCrunch that it was testing the technology, but in a limited manner and only for certain queries. “I think there is a lot of potential for applying AI to the business. We think a lot about how AI is going to change the experience at the consumer layer over time,” Airbnb co-founder Nathan Blecharczyk told TechCrunch at that time. Unlike companies like OpenAI, Google, Perplexity and the slew of startups building AI agents (AI tools that can perform tasks on a user’s behalf), Airbnb seems to be taking a more measured approach with AI. Chesky said in February that the company would use AI for customer service before it started implementing it for other uses like travel planning or booking tickets, as he believes the technology is still in its early days. Meanwhile, its competitors Expedia and Booking.com are investing heavily on the technology, launching AI features like building itineraries, trip planning, and real-time updates for travel. Techcrunch event Exhibit at TechCrunch Sessions: AI Secure your spot at TC Sessions: AI and show 1,200+ decision-makers what you’ve built — without the big spend. Available through May 9 or while tables last. Exhibit at TechCrunch Sessions: AI Secure your spot at TC Sessions: AI and show 1,200+ decision-makers what you’ve built — without the big spend. Available through May 9 or while tables last. Berkeley, CA | June 5 BOOK NOW Airbnb’s reported a total revenue of $2.27 billion for the first quarter, up 6% from a year earlier. The company, however, forecast current-quarter revenue slightly below analysts’ expectations, and indicated that it was expecting travel demand to slow down as the global tariff war hurts sentiment and discourages discretionary spending.

Google Sheets has long allowed users to publish their spreadsheets on the web as HTML web pages but now you can also publish spreadsheet data as downloadable PDF or Excel files. That means anyone one can download your public spreadsheets from Google Docs servers directly in PDF or Excel format without having to open the corresponding document in Google Docs or another Office application. Related: Google Docs Guide at Digital Inspiration You may either publish your entire spreadsheet on the web or just a portion of data (e.g. cells B2 to C9 will be represented as B2 ). To see this in action, download this PDF (that contains the full sheet) and then check this HTML page that has only a portion of data. You may ask how is this any different from manually uploading a PDF version of the Excel spreadsheet on a website? Well, the advantage here is that the PDF conversion happens in real-time so people who download your spreadsheets from Google servers in PDF format will always get the latest copy of data provided you have checked “Automatically republish when changes are made” at the time of publishing your sheet(s). The .xls publishing option is available for publishing all sheets only, not specific sheets or cell ranges. Similarly, you can’t publish cell ranges on PDF files. Also see: Edit Google Docs in Microsoft Office View the full article

If you need to view Excel data without Microsoft Office, just upload the file to Google Docs and it will display the spreadsheet data in the web browser. This sounds like an easy plan but the problem is that you can’t import large volumes of data into Google Docs. For instance, Google Docs would only allow you to import spreadsheets (or CSV files) that are less than 1 MB in size and have no more than 200,000 cells in 256 columns or less. These kind of size limits should be sufficient for most spreadsheets but you may find them pretty limiting in case you are planning to view large amounts of data in Google Docs like the population statistics of the world. Enter Google Fusion Tables - it’s a new product that lets you view Excel files as large as 100 MB in the browser - you can either upload the files from the desktop or pull it directly from your Google Spreadsheets gallery. Unlike Spreadsheet, Google Tables is primarily for handling tabular data so it can’t understand formulas or cell formatting. However, the system can recognize columns that contain values that can be geo-coded. For instance, if the column contains country names or street addresses, Google Tables can easily plot that data onto a world map. You can also apply filters to the data or create views so that only a subset of data is visible to the outside world. Later, you can export the edited data out of Google Fusion Tables as a CSV file. Related: Google Docs Guide & Tutorial View the full article

Unlike Microsoft Excel, there aren’t any Filter functions available in Google Spreadsheets but you can easily emulate them with simple formulae. How to Highlight and Remove Duplicates in Google sheets Filter Unique Records in Google Sheets If you like to remove duplicate rows from a table in Google Docs, use the UNIQUE formula as shown in this screencast. Click an empty cell, type =UNIQUE(, select the range of cells you want to filter and then close the parenthesis. Simple. Find Duplicate Rows in Google Docs This is a reverse case where you want to display rows in a spreadsheet that have duplicates. This requires a couple of steps: Step 1: Assuming that our original data is in columns A, B & C, go to cell D1 and write a formula to concatenate the data in three columns. We’ll use a pipe separator to distinguish between a row like “a1, b, c” and “a, 1b, c”. =CONCATENATE(A1, "|", B1, "|", C1) – drag the cell handle to fill the formula in other cells. Step 2: Sort the D column by clicking the header as shown in the screencast. Step 3: We now need a function to compare two adjacent values in column D. If the values are same, one of them is a duplicate for sure. Type this in E2: =IF(D2=D1, "Duplicate", "") – drag to fill all cells until E5. That’s it. All rows in column E that have value “Duplicate” are duplicate rows. View the full article

Would you like to send a confidential note to a friend that self-destructs after it has been read so that no one else (including your friend) can ever see that secret note again? Maybe it has a password that shouldn’t be stored anywhere. Or you are worried that the private note can get leaked online. There are web apps - like Burn Note and Privnote - that offer an easy solution. They essentially create a temporary web page that auto-expires after it has been viewed once. Dashlane is another app also lets you send encrypted notes from the desktop and the recipient gets 30 minutes of reading time after which the note will vanish forever. Alternatively, you can even use Google Docs to send secret, self-destructing messages to anyone in few easy steps. Here’s a quick video demo: Play ; Self-Destructing Messages in Google Docs It takes a minute to convert a Google Docs sheet into a self-destructing one. Click here to make a copy of the Google sheet in your own account. Once the sheet is auto-cleared, type your secret message anywhere inside the sheet and hit the Share button to send it to a friend. Make sure the permission is set to “Can Edit” in the sharing window. Your friend opens the sheet, reads the message and after 10 seconds, the entire sheet on his screen will clear itself. If you wish to send another self-destructing message, create a copy of the original sheet and repeat the steps. How does it work? The trick is simple. We have an onOpen trigger attached to the Google sheet that becomes active as soon as the sheet is opened. This trigger waits for 10 seconds (Utilities.sleep) and then runs the clear() command to empty all the cells of the sheet. Here’s the Google Apps Script code that actually makes your “secret message” vanish. ↓ function onOpen() { var time = 10; // Wait Time (in seconds) var ss = SpreadsheetApp.getActiveSpreadsheet(); ss.toast("This message will disappear after " + time + " seconds"); Utilities.sleep(time * 1000); ss.toast("We are now sending this private note to the shredder"); ss.getActiveSheet().getRange(1, 1, ss.getLastRow(), ss.getLastColumn()).clear(); } View the full article

This tutorial explains how you can easily scrape Google Search results and save the listings in a Google Spreadsheet. It can be useful for monitoring the organic search rankings of your website in Google for particular search keywords vis-a-vis other competing websites. Or you can exporting search results in a spreadsheet for deeper analysis. There are powerful command-line tools, curl and wget for example, that you can use to download Google search result pages. The HTML pages can then be parsed using Python’s Beautiful Soup library or the Simple HTML DOM parser of PHP but these methods are too technical and involve coding. The other issue is that Google is very likely to temporarily block your IP address should you send them a couple of automated scraping requests in quick succession. Google Search Scraper using Google Spreadsheets If you ever need to extract results data from Google search, there’s a free tool from Google itself that is perfect for the job. It’s called Google Docs and since it will be fetching Google search pages from within Google’s own network, the scraping requests are less likely to get blocked. The idea is simple. We have a Google Sheet that will fetch and import Google search results using the ImportXML function. It then extracts the page titles and URLs using an XPath expression and then grabs the favicon images using Google’s own favicon converter. The search scraper is available in two editions - the free edition that only fetches the top ~20 results while the premium edition downloads the top 500-1000 search results for your search keywords while preserving the ranking order. Features Free Premium Maxiumum number of Google search results fetched per query ~20 ~200-800 Details fetched from Google Search Results Web page title, URL and website favicon Web page title, search snippet (description), page URL, site’s domain and favicon Perform time limited searches No Yes Sort search results by date or by relevance No Yes Limit Google Search results by language or region (country) No Yes PDF Manual None Included Support options None Email Choose your Google Search Scraper edition Forever Free [premium_gas premium=“MMWZUKU3WA2ZW” platinum=“9F4DE545U3MBW”] Google Search inside Google Sheets To get started, open this Google sheet and copy it to your Google Drive. Enter the search query in the yellow cell and it will instantly fetch the Google search results for your keywords. And now that you have the Google Search results inside the sheet, you can export the Google Search results as a CSV file, publish the sheet as an HTML page (it will refresh automatically) or you can go a step further and write a Google Script that will send you the sheet as PDF daily. Advanced Google Scraping with Google Sheets This is a screenshot of the Premium edition. It fetches more number of search results, scrapes more information about the web pages and offers more sorting options. The search results can also be restricted to pages that were published in the last minute, hour, week, month or year. Spreadsheet Functions for Scraping Web Pages Writing a scraping tool with Google sheets is simple and involve a few formulas and built-in functions. Here’s how it was done: Construct the Google Search URL with the search query and sorting parameters. You can also use advanced Google search operators like site, inurl, around and others. https://www.google.com/search?q=Edward+Snowden&num=10 Get the title of pages in search results using the XPath //h3 (in Google search results, all titles are served inside the H3 tag). =IMPORTXML(STEP1, “//h3[@class=‘r’]”) Find the XPath of any element using Chrome Dev Tools 7. Get the URL of pages in search results using another XPath expression =IMPORTXML(STEP1, “//h3/a/@href”) All external URLs in Google Search results have tracking enabled and we’ll use Regular Expression to extract clean URLs. =REGEXEXTRACT(STEP3, ”\/url\?q=(.+)&sa”) Now that we have the page URL, we can again use Regular Expression to extract the website domain from the URL. =REGEXEXTRACT(STEP4, “https?:\/\/(.[^\/]+)”) And finally, we can use this website with Google’s S2 Favicon converter to show the favicon image of the website in the sheet. The 2nd parameter is set to 4 since we want the favicon images to fit in 16x16 pixels. =IMAGE(CONCAT(“http://www.google.com/s2/favicons?domain=”, STEP5), 4, 16, 16) View the full article

Introducing Twitter Merge, a new Twitter app that will help you send personalized tweets and direct messages (or DMs) to multiple Twitter users in one-go. You create a template for the tweet, specify a list of Twitter users and the app will take care of the rest. The Twitter Merge app, like Gmail Mail Merge, also runs inside a Google Spreadsheet but instead of emails, this one allows you to send customized tweets and DMs in bulk. Let me share a scenario where such an app may be useful. Say you are a brand and you are trying to organize meetings with Twitter users in various cities on different dates. You want to send them invites on Twitter but it would take just too much effort to compose the tweets or DMs manually. You thus create an invite template and add the Twitter user names in a Google Sheet. The Twitter Merge app, using that template, will send a personalized tweet (or DM) to every handle listed in the sheet automatically. Here’s a sample Tweet template and the corresponding tweets / DMs generated from the template. To create a Twitter template, write a tweet in notepad (or any text editor) and replace the words that will be different in each tweet with {{variables}}. For instance, if you are write a tweet with customized name and city, your template will read something like hello {{first name}} from {{city name}} while the “First Name” and “City Name” will be columns in your Google Spreadsheet. Bulk Send Tweets & Direct Messages Follow these steps to get started: Click here to copy the Twitter Merge spreadsheet in your Google Drive. Add new columns to the sheet so that you have one for every variable that exists in your tweet template. The column names should be the same as the variable name though the case doesn’t matter. Fill the spreadsheet with one or more rows. Add the Twitter screen names in the “Twitter User” column while keeping the “Tweet” and “Status” columns as blank. Go to the Twitter Merge menu at the top and choose Authorize. This is required because you need to allow the Spreadsheet to send tweets on our behalf. From the same menu, choose Configure and paste the text of your tweet template. You also need to specify whether the tweets area be sent as DMs or public tweets. Save the configuration. We are almost done now. The “Tweet” column will display the actual text that will be sent and you have an option to manually edit that text. Choose Send tweets from the Twitter menu and the app will send the customized tweets to all the listed Twitter users. The following video (link) will also guide you through the process. Play ; Also see: Archive Twitter Search Results in Google sheets. Later, if you wish to send more tweets or DMs, you can just repeat from step 2 onwards. Also, if the “Status” column for a tweet is set as “SENT”, the app will skip sending a tweet to that particular Twitter user. Internally, there’s a Google Script running that transforms your template into actual tweets using data from the row and it then connects to the Twitter API to send the tweets. Give it a try and do share your feedback in the comments section below. View the full article

The previous example shows how to convert Google Sheets to XLS format using the Google Drive API. The response file resource includes exportLinks URLs for the various export formats for Google Spreadsheets. For instance, the Microsoft Excel version of the Google Sheet can be retrieved via this link: file['exportLinks']['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'] You need to enabled Advanced Drive API in your Google Developers Console project to know the Export URL of a Google Drive file but there’s a way to get the Excel version using the DriveApp service as well. The getGoogleSpreadsheetAsExcel() method will convert the current Google Spreadsheet to Excel XLSX format and then emails the file as an attachment to the specified user. function getGoogleSpreadsheetAsExcel() { try { var ss = SpreadsheetApp.getActive(); var url = 'https://docs.google.com/feeds/download/spreadsheets/Export?key=' + ss.getId() + '&exportFormat=xlsx'; var params = { method: 'get', headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() }, muteHttpExceptions: true, }; var blob = UrlFetchApp.fetch(url, params).getBlob(); blob.setName(ss.getName() + '.xlsx'); MailApp.sendEmail('[email protected]', 'Google Sheet to Excel', 'The XLSX file is attached', { attachments: [blob] }); } catch (f) { Logger.log(f.toString()); } } View the full article

You have a master sheet in a Google Spreadsheet and you want to use it as a template. The script duplicates the master sheet and renames the new sheet. If another sheet exists with the same name, it can either be deleted or you can archive it by renaming it. function cloneGoogleSheet() { var name = 'labnol'; var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('Template').copyTo(ss); /* Before cloning the sheet, delete any previous copy */ var old = ss.getSheetByName(name); if (old) ss.deleteSheet(old); // or old.setName(new Name); SpreadsheetApp.flush(); // Utilities.sleep(2000); sheet.setName(company); /* Make the new sheet active */ ss.setActiveSheet(sheet); } View the full article

A client wants to receive email notifications as soon as someone edits a Google Spreadsheet that is shared with a team of people. Google Docs supports the onEdit() trigger that runs whenever an edit is made to any cell of the sheet but a limitation is that the onEdit trigger cannot send emails. Nor can be used to call external API though the URLFetch service. As a workaround, the edits were stored as a Property and another time-based trigger would periodically send the stored value by email. /** * @OnlyCurrentDoc */ function onEdit(e) { var key = "ROWCHANGES"; var range = e.range; var date = Utilities.formatDate(new Date(), e.source.getSpreadsheetTimeZone(), "dd-MM-yy HH:MM:s"); var properties = PropertiesService.getUserProperties(); var sheet = e.source.getActiveSheet(); var data = sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).getValues()[0]; data[range.getColumn() - 1] = "" + data[range.getColumn() - 1] + ""; var edits = { name: sheet.getSheetName(), data: data }; var existing = JSON.parse(properties.getProperty(key)) || {}; existing[date] = edits; properties.setProperty(key, JSON.stringify(existing)); } function onEdit_Email() { var properties = PropertiesService.getUserProperties(); var json = JSON.parse(properties.getProperty("ROWCHANGES")); var html = "": for (var keys in json) { html = html + " [" + keys + "][" + json[keys].name + "] — " + json[keys].data; } if (html !== "") { MailApp.sendEmail(email, subject, "", { htmlBody: html }); properties.deleteAllProperties(); } } View the full article

Google Spreadsheets include an import features to help you copy subsheets from another spreadsheet into the currently open sheet. If you however need to merge multiple sheets, Google Scripts can help. Put them all in one folder and run a script that will create a master sheet will all the sheets pulled from other sheets. function mergeSheets() { /* Retrieve the desired folder */ var myFolder = DriveApp.getFoldersByName(SOURCE).next(); /* Get all spreadsheets that resided on that folder */ var spreadSheets = myFolder.getFilesByType('application/vnd.google-apps.spreadsheet'); /* Create the new spreadsheet that you store other sheets */ var newSpreadSheet = SpreadsheetApp.create('Merged Sheets'); /* Iterate over the spreadsheets over the folder */ while (spreadSheets.hasNext()) { var sheet = spreadSheets.next(); /* Open the spreadsheet */ var spreadSheet = SpreadsheetApp.openById(sheet.getId()); /* Get all its sheets */ for (var y in spreadSheet.getSheets()) { /* Copy the sheet to the new merged Spread Sheet */ spreadSheet.getSheets()[y].copyTo(newSpreadSheet); } } } View the full article

A recent project involved pulling payments, invoices and accounting data from QuickBooks online into a Google Spreadsheet in near real-time. The integration was done through Google Apps Script and the QuickBooks API (v3). You also need to include OAuth 1.0 library in your Google Script project (QBO doesn’t support the OAuth 2.0 protocol yet). To get started, go to your QuickBooks Sandbox, create a sample app and get the Consumer Key and Consumer Secret. Next authorize the connection to let Google Sheets access your company inside QuickBooks. The companyId will be stored as a property inside Google Scripts and all subsequent API calls will be made for the authorized company. Here’s a sample snippet that fetches the invoices data from QuickBooks into a Google Spreadsheet. We’ve added a filter in the SELECT query to only fetch invoices that were created in the last hour. You can set this is a time-based trigger to auto-fetch QuickBooks data into the spreadsheet. function getInvoicesFromQuickBooks() { try { var service = getQuickBooksService_(); if (!service || !service.hasAccess()) { Logger.log('Please authorize'); return; } var props = PropertiesService.getUserProperties(), companyId = props.getProperty('QuickBooks.companyID'); var date = new Date(new Date().getTime() - 1000 * 60 * 60).toISOString(); var query = "SELECT * FROM Invoice WHERE Metadata.CreateTime > '" + date + "'"; var url = 'https://quickbooks.api.intuit.com/v3/company/'; url = +companyId + '/query?query=' + encodeURIComponent(query); var response = service.fetch(url, { muteHttpExceptions: true, contentType: 'application/json', headers: { Accept: 'application/json', }, }); var result = JSON.parse(response.getContentText()); var invoices = result.QueryResponse.Invoice; for (var i = 0; i < invoices.length; i++) { var Invoice = invoices[i]; sheet.appendRow([ Invoice.Id, Invoice.time, Invoice.Deposit, Invoice.DocNumber, Invoice.DepartmentRef.name, Invoice.CustomerRef.name, Invoice.ShipAddr.Line1, JSON.stringify(Invoice.Line), Invoice.ShipDate, Invoice.TrackingNum, Invoice.PaymentMethodRef.name, Invoice.TotalAmt, Invoice.Balance, ]); } } catch (f) { log_('INVOICES ERROR: ' + f.toString()); } } The script can be further enhanced to extract details of individual line items like the SKU / Part number, Quantity left, and so. This would however require a separate Rest API call to the following endpoint. https://quickbooks.api.intuit.com/v3/company/companyId/item/' + itemId View the full article

The Google Script will download your Fitbit data via the Fitbit API and insert it into a Google spreadsheet. The first row of the spreadsheet will be a header row containing data element names like steps walked, body fat, calories burned, etc. Subsequent rows will contain data, one day per row. This is a Google Spreadsheet bound script so you need to create a sheet first and put this code inside the Script editor. Fitbit uses metric units (weight, distance) so you may wish to convert them as per your locale. /* Original Fitbit script by [email protected], Further modifications by Mark Leavitt, Christian Stade-Schuldt, Robert Furberg, Amit Agarwal */ // Key of ScriptProperty for Fitbit consumer key. var CONSUMER_KEY_PROPERTY_NAME = 'fitbitConsumerKey'; // Key of ScriptProperty for Fitbit consumer secret. var CONSUMER_SECRET_PROPERTY_NAME = 'fitbitConsumerSecret'; // Default loggable resources (from Fitbit API docs). var LOGGABLES = [ 'activities/steps', 'activities/distance', 'activities/floors', 'activities/elevation', 'activities/calories', 'activities/activityCalories', 'activities/minutesSedentary', 'activities/minutesLightlyActive', 'activities/minutesFairlyActive', 'activities/minutesVeryActive', 'sleep/startTime', 'sleep/timeInBed', 'sleep/minutesAsleep', 'sleep/awakeningsCount', 'sleep/minutesAwake', 'sleep/minutesToFallAsleep', 'sleep/minutesAfterWakeup', 'sleep/efficiency', 'body/weight', 'body/bmi', 'body/fat', ]; // function authorize() makes a call to the Fitbit API to fetch the user profile function authorize() { var oAuthConfig = UrlFetchApp.addOAuthService('fitbit'); oAuthConfig.setAccessTokenUrl('https://api.fitbit.com/oauth/access_token'); oAuthConfig.setRequestTokenUrl('https://api.fitbit.com/oauth/request_token'); oAuthConfig.setAuthorizationUrl('https://api.fitbit.com/oauth/authorize'); oAuthConfig.setConsumerKey(getConsumerKey()); oAuthConfig.setConsumerSecret(getConsumerSecret()); var options = { oAuthServiceName: 'fitbit', oAuthUseToken: 'always', }; // get the profile to force authentication Logger.log('Function authorize() is attempting a fetch...'); try { var result = UrlFetchApp.fetch('https://api.fitbit.com/1/user/-/profile.json', options); var o = Utilities.jsonParse(result.getContentText()); return o.user; } catch (exception) { Logger.log(exception); Browser.msgBox('Error attempting authorization'); return null; } } // function setup accepts and stores the Consumer Key, Consumer Secret, firstDate, and list of Data Elements function setup() { var doc = SpreadsheetApp.getActiveSpreadsheet(); var app = UiApp.createApplication().setTitle('Setup Fitbit Download'); app.setStyleAttribute('padding', '10px'); var consumerKeyLabel = app.createLabel('Fitbit OAuth Consumer Key:*'); var consumerKey = app.createTextBox(); consumerKey.setName('consumerKey'); consumerKey.setWidth('100%'); consumerKey.setText(getConsumerKey()); var consumerSecretLabel = app.createLabel('Fitbit OAuth Consumer Secret:*'); var consumerSecret = app.createTextBox(); consumerSecret.setName('consumerSecret'); consumerSecret.setWidth('100%'); consumerSecret.setText(getConsumerSecret()); var firstDate = app.createTextBox().setId('firstDate').setName('firstDate'); firstDate.setName('firstDate'); firstDate.setWidth('100%'); firstDate.setText(getFirstDate()); // add listbox to select data elements var loggables = app.createListBox(true).setId('loggables').setName('loggables'); loggables.setVisibleItemCount(4); // add all possible elements (in array LOGGABLES) var logIndex = 0; for (var resource in LOGGABLES) { loggables.addItem(LOGGABLES[resource]); // check if this resource is in the getLoggables list if (getLoggables().indexOf(LOGGABLES[resource]) > -1) { // if so, pre-select it loggables.setItemSelected(logIndex, true); } logIndex++; } // create the save handler and button var saveHandler = app.createServerClickHandler('saveSetup'); var saveButton = app.createButton('Save Setup', saveHandler); // put the controls in a grid var listPanel = app.createGrid(6, 3); listPanel.setWidget(1, 0, consumerKeyLabel); listPanel.setWidget(1, 1, consumerKey); listPanel.setWidget(2, 0, consumerSecretLabel); listPanel.setWidget(2, 1, consumerSecret); listPanel.setWidget(3, 0, app.createLabel(' * (obtain these at dev.fitbit.com)')); listPanel.setWidget(4, 0, app.createLabel('Start Date for download (yyyy-mm-dd)')); listPanel.setWidget(4, 1, firstDate); listPanel.setWidget(5, 0, app.createLabel('Data Elements to download:')); listPanel.setWidget(5, 1, loggables); // Ensure that all controls in the grid are handled saveHandler.addCallbackElement(listPanel); // Build a FlowPanel, adding the grid and the save button var dialogPanel = app.createFlowPanel(); dialogPanel.add(listPanel); dialogPanel.add(saveButton); app.add(dialogPanel); doc.show(app); } // function sync() is called to download all desired data from Fitbit API to the spreadsheet function sync() { // if the user has never performed setup, do it now if (!isConfigured()) { setup(); return; } var user = authorize(); var doc = SpreadsheetApp.getActiveSpreadsheet(); doc.setFrozenRows(1); var options = { oAuthServiceName: 'fitbit', oAuthUseToken: 'always', method: 'GET', }; // prepare and format today's date, and a list of desired data elements var dateString = formatToday(); var activities = getLoggables(); // for each data element, fetch a list beginning from the firstDate, ending with today for (var activity in activities) { var currentActivity = activities[activity]; try { var result = UrlFetchApp.fetch( 'https://api.fitbit.com/1/user/-/' + currentActivity + '/date/' + getFirstDate() + '/' + dateString + '.json', options ); } catch (exception) { Logger.log(exception); Browser.msgBox('Error downloading ' + currentActivity); } var o = Utilities.jsonParse(result.getContentText()); // set title var titleCell = doc.getRange('a1'); titleCell.setValue('date'); var cell = doc.getRange('a2'); // fill the spreadsheet with the data var index = 0; for (var i in o) { // set title for this column var title = i.substring(i.lastIndexOf('-') + 1); titleCell.offset(0, 1 + activity * 1.0).setValue(title); var row = o[i]; for (var j in row) { var val = row[j]; cell.offset(index, 0).setValue(val['dateTime']); // set the date index cell.offset(index, 1 + activity * 1.0).setValue(val['value']); // set the value index index index++; } } } } function isConfigured() { return getConsumerKey() != '' && getConsumerSecret() != ''; } function setConsumerKey(key) { ScriptProperties.setProperty(CONSUMER_KEY_PROPERTY_NAME, key); } function getConsumerKey() { var key = ScriptProperties.getProperty(CONSUMER_KEY_PROPERTY_NAME); if (key == null) { key = ''; } return key; } function setLoggables(loggable) { ScriptProperties.setProperty('loggables', loggable); } function getLoggables() { var loggable = ScriptProperties.getProperty('loggables'); if (loggable == null) { loggable = LOGGABLES; } else { loggable = loggable.split(','); } return loggable; } function setFirstDate(firstDate) { ScriptProperties.setProperty('firstDate', firstDate); } function getFirstDate() { var firstDate = ScriptProperties.getProperty('firstDate'); if (firstDate == null) { firstDate = '2012-01-01'; } return firstDate; } function formatToday() { var todayDate = new Date(); return ( todayDate.getFullYear() + '-' + ('00' + (todayDate.getMonth() + 1)).slice(-2) + '-' + ('00' + todayDate.getDate()).slice(-2) ); } function setConsumerSecret(secret) { ScriptProperties.setProperty(CONSUMER_SECRET_PROPERTY_NAME, secret); } function getConsumerSecret() { var secret = ScriptProperties.getProperty(CONSUMER_SECRET_PROPERTY_NAME); if (secret == null) { secret = ''; } return secret; } // function saveSetup saves the setup params from the UI function saveSetup(e) { setConsumerKey(e.parameter.consumerKey); setConsumerSecret(e.parameter.consumerSecret); setLoggables(e.parameter.loggables); setFirstDate(e.parameter.firstDate); var app = UiApp.getActiveApplication(); app.close(); return app; } // function onOpen is called when the spreadsheet is opened; adds the Fitbit menu function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = [ { name: 'Sync', functionName: 'sync', }, { name: 'Setup', functionName: 'setup', }, { name: 'Authorize', functionName: 'authorize', }, ]; ss.addMenu('Fitbit', menuEntries); } // function onInstall is called when the script is installed (obsolete?) function onInstall() { onOpen(); } View the full article

You have a Google Spreadsheet and you need to programmatically find if a cell with particular value exists in that sheet. Well, Google Scripts can help. You can either search cells in a single column (like A) or the script can search for all cells in the row and return the index of the matching row. function findInColumn(column, data) { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var column = sheet.getRange(column + ':' + column); // like A:A var values = column.getValues(); var row = 0; while (values[row] && values[row][0] !== data) { row++; } if (values[row][0] === data) return row + 1; else return -1; } function findInRow(data) { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var rows = sheet.getDataRange.getValues(); for (var r = 0; r < rows.length; r++) { if (rows[r].join('#').indexOf(data) !== -1) { return r + 1; } } return -1; } View the full article

The previous snippet on getting Spreadsheets as JSON required you to make a sheet public and also publish as HTML before other apps can pull data as JSON from the sheet. If you would not like to make your Google spreadsheet public, you can use Google Apps Script to create a web app that will expose the sheet data as JSON. /* Source: https://gist.github.com/daichan4649/8877801 */ function doGet(e) { var sheetName = "Sheet 1"; var sheetId = "1234..."; var book = SpreadsheetApp.openById(sheetId); var sheet = book.getSheetByName(sheetName); var json = convertSheet2JsonText(sheet); return ContentService .createTextOutput(JSON.stringify(json)) .setMimeType(ContentService.MimeType.JSON); } function convertSheet2JsonText(sheet) { // first line(title) var colStartIndex = 1; var rowNum = 1; var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn()); var firstRowValues = firstRange.getValues(); var titleColumns = firstRowValues[0]; // after the second line(data) var lastRow = sheet.getLastRow(); var rowValues = []; for(var rowIndex=2; rowIndex<=lastRow; rowIndex++) { var colStartIndex = 1; var rowNum = 1; var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn()); var values = range.getValues(); rowValues.push(values[0]); } // create json var jsonArray = []; for(var i=0; i var line = rowValues[i]; var json = new Object(); for(var j=0; j json[titleColumns[j]] = line[j]; } jsonArray.push(json); } return jsonArray; } View the full article

Amit Agarwal is a web geek, solo entrepreneur and loves making things on the Internet. Google recently awarded him the Google Developer Expert and Google Cloud Champion title for his work on Google Workspace and Google Apps Script. View the full article

Amit Agarwal is a web geek, solo entrepreneur and loves making things on the Internet. Google recently awarded him the Google Developer Expert and Google Cloud Champion title for his work on Google Workspace and Google Apps Script. View the full article

The cloneGoogleSheet() function will copy data (all rows and columns, but no formatting styles) from one Google Spreadsheet to any other Google Spreadsheet under the same Google Drive. You need specify the file IDs of the source and destination Google Spreadsheets as arguments in the formula and also change the source and target sheet names inside the method body. This function can be invoked via a time-based trigger or run it manually from the Apps Script editor. However, if you would like to keep the two spreadsheet in sync with each other always, you an consider using the IMPORTRANGE() Google formula that automatically imports a range of cells from a specified spreadsheet into the currently selected cell / range /sheet. // copy data from Google Sheet A to Google Sheet B // Credit: @chrislkeller function cloneGoogleSheet(ssA, ssB) { // source doc var sss = SpreadsheetApp.openById(ssA); // source sheet var ss = sss.getSheetByName('Source spreadsheet'); // Get full range of data var SRange = ss.getDataRange(); // get A1 notation identifying the range var A1Range = SRange.getA1Notation(); // get the data values in range var SData = SRange.getValues(); // target spreadsheet var tss = SpreadsheetApp.openById(ssB); // target sheet var ts = tss.getSheetByName('Target Spreadsheet'); // Clear the Google Sheet before copy ts.clear({ contentsOnly: true }); // set the target range to the values of the source data ts.getRange(A1Range).setValues(SData); } View the full article

Gravity Forms is an extremely popular Forms plugin for the WordPress. When someone submits a form created with Gravity Forms, the form data is saved inside the MySQL database associated with your WordPress installation. There are paid add-ons through, Zapier for example, that let you do more when someone submits a Form. For instance, you can setup a task in Zapier that will automatically save the Gravity Form data to a specific Google Spreadsheet. Or you can setup a rule where the data is emailed to you as soon as a form is submitted. Zapier offers a visual tool to maps your Gravity Forms to Google Spreadsheets but you can do something similar with Google Apps Script and WordPress hooks without needing to subscribe to Zapier. Let me show you how: From Gravity Forms to Google Spreadsheets First we need to create a web-app with Google Scripts that will receive the Form data from Gravity Forms and either save it to Google Sheets or send it via Gmail. Also see: Get Google Forms Data in Email. Open the Google Spreadsheet where you wish to save the Forms data and create a header row with the column names for all the fields that you wish to save from Gravity Forms. Next go to Tools, Script Editor and paste the following snippet. function doPost(e) { if (!e) return; var sheetID = 'GOOGLE_SPREADSHEET_ID'; // Replace this with the Google Spreadsheet ID var sheetName = 'Form Responses'; // Replace this with the sheet name inside the Spreadsheet var status = {}; // Code based on Martin Hawksey (@mhawksey)'s snippet var lock = LockService.getScriptLock(); lock.waitLock(30000); try { var sheet = SpreadsheetApp.openById(sheetID).getSheetByName(sheetName); var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; // Add the data and time when the Gravity Form was submitted var column, row = [], input = { timestamp: new Date(), }; for (var keys in e.parameter) { input[normalize_(keys)] = e.parameter[keys]; } for (i in headers) { column = normalize_(headers[i]); row.push(input[column] || ''); } if (row.length) { sheet.appendRow(row); status = { result: 'success', message: 'Row added at position ' + sheet.getLastRow(), }; } else { status = { result: 'error', message: 'No data was entered', }; } } catch (e) { status = { result: 'error', message: e.toString(), }; } finally { lock.releaseLock(); } return ContentService.createTextOutput(JSON.stringify(status)).setMimeType(ContentService.MimeType.JSON); } function normalize_(str) { return str.replace(/[^\w]/g, '').toLowerCase(); } Save the Google Script. Go to the Run menu and choose doPost to authorize the Google Scripts. Next choose Publish, Deploy as web app and save your work. Click Save New Version, set access as Anyone, even anonymous and click Deploy. Make a note of the Google Script URL as we will need it in the WordPress snippet. From WordPress to Google Spreadsheets Now we need to write an Action Hook on WordPress side that will send the data to Google Script which will then save the data to Google Spreadsheet. Go your WordPress theme folder and paste this snippet inside your functions.php file. <?php /* Replace XXX with your Gravity Form ID. e.g., gform_after_submission_2 for Form 2 */ add_action('gform_after_submission_XXX', 'add_to_google_spreadsheet', 10, 2); function add_to_google_spreadsheet($entry, $form) { // This is the web app URL of your Google Script create in previous step $post_url = 'https://script.google.com/macros/s/XYZ/exec'; // Put all the form fields (names and values) in this array $body = array('name' => rgar($entry, '1'), 'age' => rgar($entry, '2'), 'sex' => rgar($entry, '3'),); // Send the data to Google Spreadsheet via HTTP POST request $request = new WP_Http(); $response = $request->request($post_url, array('method' => 'POST', 'sslverify' => false, 'body' => $body)); } ?> Save the PHP file and submit a test entry. It should show up in your Google Spreadsheet instantly. View the full article

The Goodreads API helps you query the entire database of books on the Goodreads website. You can find the ratings of books, fetch book reviews, search books by author or even publish your own reviews. This example shows how to connect to the GoodReads website through Google Apps Script, find books by title, parse the XML results as JSON and write the results in a Google Spreadsheet. You can also extend the code to insert the thumbnail of the book image in a spreadsheet cell using the IMAGE function. To get started, go to the Goodreads.com account and create a key. All Rest API methods will require you to register for a developer key. Goodreads will return the response in XML format (see below) and we can use the XML service of Google Apps Script to easily parse this XML response. Here’s the complete example. Remember to replace the API key with your own. function GoodReads() { var search = 'Taj Mahal'; var books = searchBooks_(search); // Write Data to Google Spreadsheet. var sheet = SpreadsheetApp.getActiveSheet(); books.forEach(function (book) { sheet.appendRow([book.title, book.author, book.rating, book.url]); }); } function searchBooks_(query) { var baseUrl = 'https://www.goodreads.com/book/show/', apiUrl = 'https://www.goodreads.com/search/index.xml', apiKey = 'ctrlq.org', searchResults = [], payload = { q: query, key: apiKey }, params = { method: 'GET', payload: payload, muteHttpExceptions: true }; var response = UrlFetchApp.fetch(apiUrl, params); // API Connection Successful if (response.getResponseCode() === 200) { // Parse XML Response var xml = XmlService.parse(response.getContentText()); var results = xml.getRootElement().getChildren('search')[0]; // Save the result in JSON format results .getChild('results') .getChildren() .forEach(function (result) { result.getChildren('best_book').forEach(function (book) { searchResults.push({ title: book.getChild('title').getText(), author: book.getChild('author').getChild('name').getText(), thumbnail: book.getChild('image_url').getText(), rating: result.getChild('average_rating').getText(), url: baseUrl + result.getChild('id').getText() }); }); }); } return searchResults; } View the full article

Dan Thareja has written a Google Script that will let you export all the formulas, cell values and notes from a Google Spreadsheet as a JSON file. You can also pull the Google formulas in another web app or Google Apps script using a GET Request. It should come handy when you are reviewing the formulas of a large sheet. // Credit: github.com/danthareja function doGet(request) { // ID of Google Spreadsheet var json = getNotesAndFormulas(request.parameter.id); return ContentService.createTextOutput(JSON.stringify(cache)).setMimeType(ContentService.MimeType.JSON); } function getNotesAndFormulas(spreadsheetId) { return SpreadsheetApp.openById(spreadsheetId) .getSheets() .reduce( function (cache, sheet) { var sheetData = (cache[sheet.getName()] = {}); var range = sheet.getDataRange(); sheetData.range = range.getA1Notation(); sheetData.notes = range.getNotes(); sheetData.formulas = range.getFormulas(); return cache; }, { spreadsheetId: spreadsheetId } ); } View the full article