Google Sheets Merge

I am attempting to merge spreadsheets in a folder into a single master document. The following script works except for it splits each spreadsheet into a new sheet on the master document. Is it possible to merge them into a single sheet? function myFunction() { /* Retrieve the desired folder */ var myFolder = DriveApp.getFolderById("1RyWUxYLMuOvYUuooy7f41w1zMGntGmg7"); /* Get all spreadsheets that resided on that folder */ var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet"); /* Create the new spreadsheet th...Read more

import - IMPORTXML Google Sheets Fetch URL Error (Need script trigger help on open only)

My date source is: https://walletinvestor.com/forecast/achain-predictionOn the website they have three main numbers that I am trying to import to my Google spreadsheet (Current Price, 1-Year Forecast, 5-Year Forecast).In cell C3 I have:https://walletinvestor.com/forecast/achain-predictionMy D column is going to be used to import "Current Price" number, my E column is going to be used to import "1-Year Forecast" number, and my F column is going to be used to import "5-Year Forecast" number.This is the code I am using in D3 to import the three nu...Read more

google apps script - UrlFetchApp Post Variables Not being sent/received by php server

var headers = { "Accept": "application/json", "Content-Type": "application/json" }; var data = { action: "test123" }; var options = { "method":"POST", "headers" : headers, "followRedirects": false, "validateHttpsCertificates": false, "muteHttpExceptions" : true, "contentType": "application/json", "payload": JSON.stringify(data) };var requestURL = "http://requesturl.com/controller.php";var response = UrlFetchApp...Read more

Copy Value and Format to new sheet in Google sheets using a script

In Google Apps Script, Im using the script below to create a pdf and send as an email.The problem is that when it creates the new sheet and copies across, the formulas are copied. The emailed PDF than shows a REF error.I need to copy the Value and Format.Thanks function exportPDF() {var originalSpreadsheet = SpreadsheetApp.getActive();var message = "Please see attached"; var projectname = originalSpreadsheet.getRange("a2:c2").getValues(); var period = originalSpreadsheet.getRange("B24:c24").getValues(); var subject = projectname + " - Daily Con...Read more

google apps script - Split multi-line cells into different rows

I have a script with a function which runs through a range of rows in a Google spreadsheet and splits multi-line cells in column F into multiple rows. It then proceeds to copy down the cells in other columns of the same row to the newly created rows. Following is an example of what I mean.The range through which the function runs is this:The function runs and produces the following:As you can see, in the first image, some cells in column F have more than one line. The function detects those, splits them into as many rows as there are line break...Read more

How can I record the contents of a Google Sheets cell at the end of each day?

In Google Sheets I'm collecting data dynamically using the IMPORTHTML function, and one element from that data set is currently copied in cell C1.In another sheet I have a list of dates from now until 2017 as column A.At the end of each day, I'd like to record the final C1 score in column B next to the relevant date.How would I go about doing that in an automated way? That is, I want the spreadsheet to complete itself at the end of each day....Read more

google sheets notes via script based on lookup

This might be a complete failure for me, but I figured i'd ask anyway and see if it leads me to my answer.What i want to do is have a script examine the cell i'm in essentially do a VLookup on a table in another worksheet in the same workbook, put the values found from that lookup into a comment/note for that cell.I'd need a macro to do this the first time, and i'd want the script to run on change any other time.The google spreadsheet is being used as a schedule. Columns are grouped by day, and each day has several shifts. Rows indicate the h...Read more

Emails within Google Sheets

I am hoping i can find a kind hearted person to help me if possible with some google sheets scripting.I am looking for a way to automate something i currently have to do manually everyday. I take data from present day I have entered into a spreadsheet, copy it, paste it into and email and send it. I have used the Query function within Google sheets to help me separate who gets what information, that has made my life a tad bit easier.I currently go to each tab, copy data, go to Gmail, paste data, type in the email address of the recipient, typ...Read more

Google Sheets Script Timeout Workaround

I'm building a script that uses a looping ImportHTML command to web scrape weather data based on zip code, and am currently running into an issue with the execution timing out every time the script is run.The current way I have the script set up produces a correct result when run, but given that the script is pulling data from several hundred sources, it is taking a while and will not complete within the current time limit of Google scripts.The sheet running the script utilizes 3 tabs:ZIPS, which contains a list of zip code link values pulled f...Read more

Appending or inserting footnotes programmatically into google doc using google app script

I am using Google App Script to generate a new google doc. The contents of the document come from an array that is looped and paragraph elements are added, or text elements are inserted/appended to pre-existing paragraph elements that were earlier in the loop. The issue is: some of the items in the array need to be appended or inserted as footnote elements. The documentation says that footnote elements are contained within listItem or paragraph elements. However there does not seem to be a method to insert footnotes programmatically in the same...Read more

Google script -- adding days to a date

I realize this is likely very easy but I'm a struggling newbie. I have dates in spreadsheet cells D3, D4, and D5 (mm/dd/yyyy) and simply need a macro to add 7 days to each of them. I've managed to mangle together some code that may be working but the output is in milliseconds rather than mm/dd/yyyy format.I can't seem to get the syntax right to convert it. Any hints? Thank you!function UpdateDates() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var First = new Date(); var Second = new Date(); var Thi...Read more

Google Script not running sequentially

I'm having trouble with some creating some functions in Google Script. I'm trying to query a range then copy and paste it's values, however it doesn't seem to load even when I put a sleep timer in between. It does, however run when I comment out the copy and paste. Any help is appreciated!function setAndCopy(sheetName){ var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); sheet.getRange("B3:P").clearContent(); var targetCell = sheet.getRange("B3"); var formulaString = "=query(Vehicles!A2:AM,\"Select A,B,C,D,E,K where...Read more

visual studio code - Enabling autocomplete for Google Apps Script in locally-installed IDE

I'm trying to build GAS projects locally using clasphttps://github.com/google/clasp Any locally-installed IDE is a huge improvement over Google's Script Editor, so the tool looks very promising. Unfortunately, the autocomplete feature for GAS services doesn't seem to be included in the package. The documentation says: The Apps Script CLI uses TypeScript to provide autocompletion and linting when developing. Use an IDE like Visual Studio Code for TypeScript autocompletion.After going through the steps and installing all required dependencies, ...Read more

How to limit Google Form submissions based on total number of answers not number of responses

I created a simple registration form that only asks for:Email address (required)Name (required)Name of plus one person (not required - if there is one)I can limit a Google Form based on the number of responses:function closeForm() { // get active form var form = FormApp.getActiveForm(); // retrieve number of responses thusfar var responses = form.getResponses(); // set close message var msg = "We're sorry, the event is full."; // do the math if(responses.length >= 100) { form.setAcceptingResponses(false).setCus...Read more

Google Scripts Sheets hide/ unhide

In writing a 'custom function' Google Script for my particular sheet, I simply want to hide a column:function hideColumn(index) { // get active spreadsheet var ss = SpreadsheetApp.getActiveSpreadsheet(); // get first sheet var sheet = ss.getSheets()[0]; sheet.hideColumns(index);}This code works fine when I run it from within the Script editor but if I try to run it from inside a cell "=hideColumn(2)", I get the following error: "You do not have permission to call hideColumns (line 48)."From the same sheet/ script I'm able to run other cust...Read more