New answers tagged google-apps-script
2
Google Sheets has hard-and-fast limitations. One of them is that no spreadsheet may have more than five million cells total among all sheets/tabs. If you are trying to upload 1M rows, and those rows each contain more than 5 cells each (which is likely), you'll have exceeded this number (in fact, 1M rows with 5 cells each completely maxes the space limitation)...
-2
It is also possible to pull data from a JSON file to Google Sheets without using Google Apps Script.
What you need to do, is to install add-on, then fill out the fields, set up an automatic data schedule and run it for the first time (to make sure it fetches all data correctly). I attach screenshot of the form itself
0
I figured out the answer. Hopefully it helps someone else.
var fileList = ["www.file1.com", "www.file2.com", "www.file3.com"];
var richText = SpreadsheetApp.newRichTextValue();
var text = "";
var links = [];
for (i = 0; i < fileList.length; i++) {
if (i == 0) { // Sets the start of the link.
var start = text....
1
You would need to use a formula with the SUBSTITUTE function
=SUBSTITUTE(D8,"https://drive.google.com/open?id=",
"https://drive.google.com/uc?export=view&id=")
0
From the question
Is there a way to format the date into MM-DD-YYYY and change the time zone based on the location I am in?
Server side runs on Google servers, so JavaScript methods like toLocaleDateString() use the Google servers locale instead of the locale of the user.
Instead of toLocaleDateString() you could use Utilities.formatDate(date, timezone, ...
0
You want to extract a report from "Loads" based records that fall between a given "start date" and "end date".
There might be several ways to do this. This answer uses the QUERY function to display the record details.
Use this formula:
=query(Loads!A2:C,"select A, B, C where A>=date '"&TEXT(B1,"yyyy-mm-dd&...
0
Explanation:
You need to make two modifications:
Use getSheets to get all the sheets of your spreadsheet file.
Define a list of sheet names you would like to execute your current solution to:
var selectedSheets = ["Sheet1","Sheet5","Test2"];
Create a for loop (in this case forEach) and iterate over the sheets.
Finally (...
2
You can do the following set-up
In cell C2 place ="'statistics'!d6" and so on
Then in cell D2 the below formula and drag it down
=SUM(IMPORTRANGE($B$2, C2),IMPORTRANGE($B$4, C2))
0
Explanation:
You confuse Google Sheet Formulas like ISBLANK with Google Apps Script which uses JavaScript.
In JavaScript there is no built in method ISBLANK nor it is defined in your code and hence the error: this method is not defined.
To delete every column with an empty header, all you need to do in JavaScript is to check whether the header value is empty ...
1
Use google colab to find the number of files in a folder
Go to this notebook
File -> Save a copy in Drive
The new copy will open automatically
Run the code blocks by selecting and pressing Shift+Enter
Mount the google drive when you are prompted
Change the value of variable FOLDER_PATH from labelled-dataset to your own folder name or path. For example ...
-1
I've been fighting with this all day, and I've finally come up with a workaround that in my opinion is just about as stupid as the original problem: edit and save the script where the custom function is being loaded from.
I don't know how reliable this is, but I tested it with a couple strawpolls I had in a spreadsheet, and sure enough, the vote count ...
1
Issue:
The issue is not the code.
As stated in the official documentation:
The onEdit(e) trigger runs automatically when a user changes the value
of any cell in a spreadsheet.
Namely, the onEdit trigger is activated only when the user edits the
value of a particular sheet. If the edit comes from scripts or
formulas then the onEdit function won't be ...
0
You create a tick box, for example at cell C1, and then set your target cell as:
=if(C1=true, SheetName("123"),)
When you untoggle and re-toggle the tick box, the function will be called again and the result updates.
Still manual, but probably better than refreshing the page.
1
Solution 1 (recommended):
Use directly the insertImage(url, column, row) which accepts the url of the image:
function imgToSheet() {
const img_url = 'https://upload.wikimedia.org/wikipedia/commons/thumb/2/2f/Google_2015_logo.svg/368px-Google_2015_logo.svg.png';
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("Sheet1"); //...
0
This is fantastic!
I kit-bashed what you provided, changed a couple names and viola! It's a masterpiece! totally works the way I need! you ROCK!
This is the final form:
function onOpen() {
SpreadsheetApp.getUi().createMenu('buildsheets')
.addItem('buildsheets', 'buildsheets')
.addToUi()}
function buildsheets() {
var ss = ...
0
You want to duplicate a sheet containing an existing template AND give the new sheet a predetermined name.
In this answer, the list of sheet names is taken from Column A of "All shots", and the method to duplicate the template is a specifically designed for templates:
insertSheet(sheetName, options) Doc Ref
function buildsheets() {
var ss = ...
Top 50 recent answers are included
