Search the Community
Showing results for tags 'google sheets'.
-
I have a sheet with over 2000 rows. The contents of column B and C should both be in column B, so we need to merge the C with B. So I only want to merge cells horizontally. How do I do this in Microsoft Excel or Google Sheets?
- 3 replies
-
- google sheets
- sheets
-
(and 4 more)
Tagged with:
-
Hey Everyone, I've been using an addon for my Google Sheets called AutoCrat and it basically takes Google Form submissions from Google Sheets and creates a PDF document which is then sent to the respective email address within the Google spreadsheet. The problem is that the addon doesn't work anymore. The time triggers are broken and I'm in need of something that works. I've tried looking into another addon called Yet Another Mail Merge just to try to see if it works but nope. I've also tried recreating my spreadsheet from scratch and have pasted in my formulas. That didn't seem to fix the issue. So it comes down to using Javascript. 1st problem: I have 1 column of data which is that if the condition is equal to the respective row, it would generate an automated invoice and be sent out. I know there's a lot of code with a simple Google search but none of these seem to have it. 2nd problem: I have 2 templates but the code I've added to the bottom of this post only allows for 1 template. This is Autocrat's script from a few years ago: https://github.com/NewVisionsForPublicSchools/autoCrat-SCRIPT but it's too complex so I looked for some other code I could possibly work with and I found this: http://www.andrewroberts.net/2016/01/google-apps-script-to-create-and-email-a-pdf/ /* PDF Create - with rename and email ================================== When you click "Create PDF>Create PDF" this script uses the data from the active row to construct a PDF in your GDrive. The value in the "File Name" column is used to name the file and - if there is a value - it is emailed to the recipient in the "Email" column. */ // Config // ------ // 1. Create a GDoc template and put the ID here var TEMPLATE_ID = '---- UPDATE ME -----' // var TEMPLATE_ID = '1wtGEp27HNEVwImeh2as7bRNw-tO4HkwPGcAsTrSNTPc' // Demo template // Demo script - http://bit.ly/createPDF // 2. You can specify a name for the new PDF file here, or leave empty to use the // name of the template or specify the file name in the sheet var PDF_FILE_NAME = '' // 3. If an email address is specified you can email the PDF var EMAIL_SUBJECT = 'The email subject ---- UPDATE ME -----' var EMAIL_BODY = 'The email body ------ UPDATE ME ---------' // Constants // --------- // You can pull out specific columns values var FILE_NAME_COLUMN_NAME = 'File Name' var EMAIL_COLUMN_NAME = 'Email' /** * Eventhandler for spreadsheet opening - add a menu. */ function onOpen() { SpreadsheetApp .getUi() .createMenu('Create PDF') .addItem('Create PDF', 'createPdf') .addToUi() } // onOpen() /** * Take the fields from the active row in the active sheet * and, using a Google Doc template, create a PDF doc with these * fields replacing the keys in the template. The keys are identified * by having a % either side, e.g. %Name%. * * @return {Object} the completed PDF file */ function createPdf() { var ui = SpreadsheetApp.getUi() if (TEMPLATE_ID === '') { ui.alert('TEMPLATE_ID needs to be defined in code.gs') return } // Set up the docs and the spreadsheet access var copyFile = DriveApp.getFileById(TEMPLATE_ID).makeCopy(), copyId = copyFile.getId(), copyDoc = DocumentApp.openById(copyId), copyBody = copyDoc.getActiveSection(), activeSheet = SpreadsheetApp.getActiveSheet(), numberOfColumns = activeSheet.getLastColumn(), activeRowIndex = activeSheet.getActiveRange().getRowIndex(), activeRow = activeSheet.getRange(activeRowIndex, 1, 1, numberOfColumns).getValues(), headerRow = activeSheet.getRange(1, 1, 1, numberOfColumns).getValues(), columnIndex = 0, headerValue, activeCell, ID = null, recipient = null // Replace the keys with the spreadsheet values and look for a couple // of specific values for (;columnIndex < headerRow[0].length; columnIndex++) { headerValue = headerRow[0][columnIndex] activeCell = activeRow[0][columnIndex] copyBody.replaceText('%' + headerValue + '%', activeCell) if (headerValue === FILE_NAME_COLUMN_NAME) { ID = activeCell } else if (headerValue === EMAIL_COLUMN_NAME) { recipient = activeCell } } // Create the PDF file, rename it if required, delete the doc copy // and email it copyDoc.saveAndClose() var newFile = DriveApp.createFile(copyFile.getAs('application/pdf')) if (PDF_FILE_NAME !== '') { newFile.setName(PDF_FILE_NAME) } else if (ID !== null){ newFile.setName(ID) } copyFile.setTrashed(true) if (recipient !== null) { MailApp.sendEmail( recipient, EMAIL_SUBJECT, EMAIL_BODY, {attachments: [newFile]}) } ui.alert('New PDF file created in the root of your Google Drive ' + 'and emailed to ' + recipient) } // createPdf()
- 1 reply
-
- javascript
- google api
-
(and 2 more)
Tagged with:
-
Hey guys, I want to add multiple different dates and times (now()) in google sheets but every time I change something all the cells refresh with the current date and time. how can I deactivate that refresh and keep the old value in the cell? I use Sheets because I have to access it with a browser. Please help, I couldn´t find a working solution for about 2 hours and I desperately want to have it working. Helge Neumann
- 1 reply
-
- google sheets
- refresh
-
(and 1 more)
Tagged with:
-
Hi, I was wondering if I were to have Google Forms drop data into my Google Sheets, I would want a sort of ticketing system where the data validation on column C for instance, be "Open" as the data from Forms gets dropped in. Would that be possible through data validation? Is there some way to do it? My arrayformula doesn't seem to be working very well either. When the data gets dropped in from Google Forms, my formulas get wiped out and arrayformula doesn't actually do anything.
- 2 replies
-
- data validation
- default
-
(and 3 more)
Tagged with:
-
I've experienced that google sheets zooms out on the page and makes the text almost unreadable. Is there a trick to printing it out where it looks normal or at least like in Microsoft Exel? My maths teacher refuses to grade my exams if I don't fix the textsize. Thanks!
- 6 replies
-
- microsoft exel
-
(and 2 more)
Tagged with:
-
Hi all. Here I am with another trouble with Google Sheets. This time I need to know how to hide grid lines but only on a specific selection, not the whole document. I know it can be done, I have such a file but I can repeat it! Thank you in advance.
- 2 replies
-
- google sheets
- hide grid line
-
(and 1 more)
Tagged with:
-
Hi everybody. I hope there's somebody experienced in the community with Google Sheets. I've been at it since a year and a half so far but I still got a lot to learn, it's been a breath of fresh air so far since I don't have to always worry updating my office or with the usual crashes, making me loose everything. I know my ways around Excel but I'm no expert either. I'm trying to create some personalized dropdown menus, to help me facilitate choosing categories for my spending in my yearly budged file. I'm having trouble at the moment when I choose the cells where these categories are contained (they are A LOT) because in the same column where they all are located, there's also some headings I cannot move anywhere else, so at the moment of choosing them, the headings show up in the dropdown menus too as incorrect categories. Is there anyway i can bypass this? Thanks in advance.
-
- google sheets
- exclusion formula
-
(and 1 more)
Tagged with:
-
Hi, I have a large google sheets file that I need to share with about 15 colleagues. My issue is that : I would like to set a rule that if my colleague is signed it to their google account they can edit. If they are not signed in, they may only view the file. Is this possible? I'm tired of arriving early in the morning and opening this up and seeing that "Anonymous Pelican" or "Anonymous Nyan Cat" has modified a line.. Any help / Input is well appreciated. Thanks, Adam
- 4 replies
-
- excel
- spreadsheet
-
(and 2 more)
Tagged with:
-
Hi, I’m using Google Sheets as my spread sheet software of choice on my iPad Pro and I’ve been trying really hard this thing about “going iPad Pro solo” for some time. So far the learning curve is kind of steep but I’m slowly climbing it. It’s totally worth it for the portability but I’m stuck with one thing. Basically I’m trying to do sums inside a file by importing data from a sheet and summing it up in another sheet inside the same file. The purpose of this is to have a clean database on one sheet (let’s say “sheet A”) with all expenses done in a month and on another sheet (“sheet B”) to do just analysis. What I usually do on my PC is: On sheet B, in the desired cell I type “=SUM(“ ...then manually, switching to sheet A, with the mouse pointer I go and click on the cell or cell blocks of data I need and the formula autofills. The thing is in the iPadOS UI it’s not possible because the whole bottom bar where the sheets are all alligned disappears every time I type, even if I use a bluetooth keyboard case (Logitech Slim Folio Pro) so I can’t click physically to switch from sheet to sheet. I know, it’s some stupid limitations they came up with and they’re slowly testing my patience. What I’m trying to do is to bypass the whole thing by trying a conditional formula. I know they work the same in Excel, I’ve seen them use by colleagues but I never did it myself. I’ll try to explain my needs as clearly as possible if there’s anyone who can suggest me the correct formula composition. In sheet A, the expenses list, each expense have a tag field, “STATUS”, that can be either “PENDING” or “PAID”. I need the formula to add up in a desired cell on sheet B all expenses from sheet A that have the “PENDING” status. I could use a formula such as =SUM(Sheet A!J7;Sheet A!J11;...) in sheet B but it would take too long to type it for hundreds of values and I’d like to take the chance to automate the whole thing so it all just auto adds up just by changing the status tag. Does anyone know how? Thank you very much.
- 1 reply
-
- google sheets
- conditional function
-
(and 3 more)
Tagged with:
-
So I have a Google sheets layout and I need the information from a certain column in each of 5 separate tabs to auto populate onto a "master" tab. I believe this is done under data validation, though thing is; what I need is for the master sheet to update automatically when I update the information on sheets 1-5. Any assistance with this would be grateful. My brain is a muddy mess today so be gentle ...Google Docs/Sheets is not my common use for this kind of thing and it has been a long while since I have done this with MS Excel. Thank you in advance.
-
Hi all, I am using Google Sheets and I have several cells with the following formula: ="Savings: " &SUM(C3:C31) This formula enables the use of both text and a number formula in the same cell. I am wanting to add a currency symbol and colour to ONLY the resulting number in the cell. Is this possible? Thanks in advance.
-
Hello people of the internet! I am working on manipulating some data using google sheets and need a way to systematically highlight the outliers in another colour. I have figured out that this would most likely be done through advanced formatting, but I have no clue on the syntax of how I would do this, and the statistics involved. I think it would be best to highlight data outside of one standard deviation, but I am also not sure if this is the best method to go about finding the outliers... Any help that you could offer as regarding google sheets or the statistics involved in finding outliers would be MUCH appreciated. Sample of the data I am working with.
-
I have a json data source as shown below. When I do =ImportJSON("*my url to the data source*", "/Schedule/Teams/teamNumber", "noHeaders") in google sheets, i only get a list of 2585, 4355. Those teams are the first "teamNumber" in each of the "Teams". How can I get the ImportJSON function in google sheets to result with all 12 team numbers, instead of just the first one in each team? { "Schedule": [ { "description": "Qualification 1 (C)", "field": "Primary", "tournamentLevel": "Qualification", "startTime": "2016-04-08T09:00:00", "matchNumber": 1, "Teams": [ { "teamNumber": 2585, "station": "Red1", "surrogate": false }, { "teamNumber": 3545, "station": "Red2", "surrogate": false }, { "teamNumber": 4610, "station": "Red3", "surrogate": false }, { "teamNumber": 3743, "station": "Blue1", "surrogate": false }, { "teamNumber": 57, "station": "Blue2", "surrogate": false }, { "teamNumber": 2950, "station": "Blue3", "surrogate": false } ] }, { "description": "Qualification 2 (C)", "field": "Primary", "tournamentLevel": "Qualification", "startTime": "2016-04-08T09:07:00", "matchNumber": 2, "Teams": [ { "teamNumber": 4355, "station": "Red1", "surrogate": false }, { "teamNumber": 6173, "station": "Red2", "surrogate": false }, { "teamNumber": 4328, "station": "Red3", "surrogate": false }, { "teamNumber": 4155, "station": "Blue1", "surrogate": false }, { "teamNumber": 5829, "station": "Blue2", "surrogate": false }, { "teamNumber": 3345, "station": "Blue3", "surrogate": false } ] } }
-
that is the data in question i want it to show on a scatter graph. I want a scatter plot with altitude on y axis and velocity on x axis just as is in the table any help would be appreciated. ps: still collecting data so it will change over time edit: yes i know there is already a graph there Edit 2: solved, removed link.