Random gray horizontal lines on google sheets

I have the same problem as in this post, but the solution did not work for me:

Random gray horizontal lines in Google Sheets – using Chrome and Opera

I'm just using Chrome. I am in 100% zoom.

Thank you!

formulas – Get translation using Google Sheets and IMPORTXML

I want to remove the phonetic guide for a Google Translator translation using Google Sheets.

For example, if you enter the page:

https://translate.google.com/#view=home&op=translate&sl=auto&tl=zh-CN&text= 欢迎 你

… you will see a transliteration of 欢迎 你 inside Huānyíng (Welcome!). I want to extract only the transliteration. (Huanyin nǐ). If I inspect the code of the website, I see that this sentence is stored in:

Huanyin nǐ

… so I tried to extract this transliteration using IMPORTXML writing the following code:

= importxml ("https://translate.google.com/#view=home&op=translate&sl=auto&tl=zh-CN&text= 欢迎 你"; "// div[@class='tlid-transliteration-content transliteration-content full']")

But the result is #N / A. It's supposed to be that // div[@class='name-of-the-element']" I would do the trick. Where this error? How can I import this specific result?

Google Apps script – How to order different sheets for conditions?

I have 6 different sheets on my spreadsheet

I want to order the first 3 and last 2 (5 and 6) according to an ascending order that depends on the value in column H

However, the fourth sheet I want to order is based on a descending order that depends on the value in Column H.

Is that possible?

I have another script running now also where a row moves according to the value of the cell.

This is what I currently have, order everything perfect. However I need a sheet to be dependent

onEdit (e) {function

var ss = e.source,
sheet = ss.getActiveSheet (),
range = e.range,
objectives sheet,
columnNumberToWatch = 8; // column A = 1, B = 2, etc.
if (sheet.getName () === "Call Present" && e.value === "Call Completed" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Call completed"
} else if (sheet.getName () === "Call sent" && e.value === "Creating link" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Creating link"
} else if (sheet.getName () === "Call sent" && e.value === "Unlock sent" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Buy / Unlock"
} else if (sheet.getName () === "Call completed" && e.value === "Call sent" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Call sent"
} else if (sheet.getName () === "Call completed" && e.value === "Creating link" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Creating link"
} else if (sheet.getName () === "Call complete" && e.value === "Unlock sent" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Buy / Unblockoxa"
} else if (sheet.getName () === "Call Completed" && e.value === "Faded" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Faded"
} else if (sheet.getName () === "Call Present" && e.value === "Faded" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Faded"
} else if (sheet.getName () === "Creating link" && e.value === "Call sent" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Call sent"
} else if (sheet.getName () === "Creating link" && e.value === "Call completed" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Call completed"
} else if (sheet.getName () === "Creating link" && e.value === "Faded" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Faded"
} else if (sheet.getName () === "Creating link" && e.value === "Unlock sent" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Buy / Unlock"
} else if (sheet.getName () === "Buyout / Unlock" && e.value === "Ported" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "portado"
} else if (sheet.getName () === "Faded" && e.value === "Call Present" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Call sent"
} else if (sheet.getName () === "Faded" && e.value === "Call Completed" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Call sent"
} else if (sheet.getName () === "Faded" && e.value === "&& e.range.columnStart === columnNumberToWatch) {
targetSheet = "Call sent"
}
ss.getSheetByName (targetSheet)
.appendRow (sheet.getRange (e.range.rowStart, 1, 1, sheet.getLastColumn ())
.getValues ​​()[0])
sheet.deleteRow (e.range.rowStart);
}

onEdit function (event) {
sheet var = event.source.getActiveSheet ();
var editedCell = sheet.getActiveCell ();

var columnToSortBy = 8;
var tableRange = "A3: P"; // What to order

if (editedCell.getColumn () == columnToSortBy) {
var range = sheet.getRange (tableRange);
range.sort ({column: columnToSortBy, ascending: true});
}
}

Google Sheets – Run multiple scripts at the same time

I'm very new to this, so I'm not sure what to do. I currently have a spreadsheet that runs a script that allows the rows to move according to the Cell Value in column H (State)

I want to execute a long side of an automatic sort function that will also be sorted by the same H (state) column

Here is a link to the Excel file:
https://docs.google.com/spreadsheets/d/1U9LulscZNKiMd_kv9McZEGdZYbjQNhjCUcr5uWcAabo/edit?usp=sharing

I've been trying for hours 😕

onEdit (e) {function

var ss = e.source,
sheet = ss.getActiveSheet (),
range = e.range,
objectives sheet,
columnNumberToWatch = 8; // column A = 1, B = 2, etc.
if (sheet.getName () === "Call Present" && e.value === "Call Completed" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Call completed"
} else if (sheet.getName () === "Call sent" && e.value === "Creating link" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Creating link"
} else if (sheet.getName () === "Call sent" && e.value === "Unlock sent" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Buy / Unlock"
} else if (sheet.getName () === "Call completed" && e.value === "Call sent" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Call sent"
} else if (sheet.getName () === "Call completed" && e.value === "Creating link" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Creating link"
} else if (sheet.getName () === "Call complete" && e.value === "Unlock sent" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Buy / Unblockoxa"
} else if (sheet.getName () === "Call Completed" && e.value === "Faded" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Faded"
} else if (sheet.getName () === "Call Present" && e.value === "Faded" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Faded"
} else if (sheet.getName () === "Creating link" && e.value === "Call sent" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Call sent"
} else if (sheet.getName () === "Creating link" && e.value === "Call completed" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Call completed"
} else if (sheet.getName () === "Creating link" && e.value === "Faded" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Faded"
} else if (sheet.getName () === "Creating link" && e.value === "Unlock sent" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Buy / Unlock"
} else if (sheet.getName () === "Buyout / Unlock" && e.value === "Ported" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "portado"
} else if (sheet.getName () === "Faded" && e.value === "Call Present" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Call sent"
} else if (sheet.getName () === "Faded" && e.value === "Call Completed" && e.range.columnStart === columnNumberToWatch) {
targetSheet = "Call sent"
} else if (sheet.getName () === "Faded" && e.value === "&& e.range.columnStart === columnNumberToWatch) {
targetSheet = "Call sent"
}
ss.getSheetByName (targetSheet)
.appendRow (sheet.getRange (e.range.rowStart, 1, 1, sheet.getLastColumn ())
.getValues ​​()[0])
sheet.deleteRow (e.range.rowStart);
}

This was the script I was planning to use to sort this (IDK if it is correct).

Is it possible to execute this script only on the first 3 pages, and have it automatically classified in descending order on the 4th sheet (Buy / Unlock)? The last 2 does not matter?

onEdit () {function
var sheet = SpreadsheetApp.getActiveSheet ();
var range = sheet.getRange (3, 1, sheet.getLastRow () - 2, sheet.getLastColumn ());
range.sort ({column: H, ascending: false});
}

AND / OR Conditional format for Google Sheets with multiple criteria

I am trying to write a custom formula for the conditional format column E referring to the different tenure level objectives in column D to indicate if they are reaching their base of objectives outside of their tenure level. I wrote the following formula and it did not work out. Any help would be greatly appreciated.

I try to say: If D3 = "new" or "beginner" or "intermediate" or "veteran",
Then F3> = D24 or F3> = D25 or F3> = D26 or F3> = D26
.

= yes (or ($ D4 = "new", $ D4 = "beginner", $ D4 = "intermediate", $ D4 = "veteran"), O ($ E4> = $ D24, $ E4> = $ D25, $ E4> = $ D26, $ E4> = $ D27))

Google sheets missing reference operators from main range intersection + union / concatenation

1) Why are still missing Google Sheets to these main reference operators, especially Intersection?

3) And when will Google Sheets get them? – What will it take?

You can consult this Google support team directly through your forum or your comment system. (Any other discussion would be mainly a speculation based on opinion, because the spirit of the StackExchange platform is defined purely as user to user help desk)

0

2) Any temporary solution in the meantime …?

Of course, there are always some solutions. This problem of intersection could be solved by means of scripts, as well as with internal formulas in many different ways according to the exact scenario. An example would be:

= INDIRECT (ADDRESS (MATCH ("row2", A: A, 0), MATCH ("col2", 1: 1, 0), 4))

0

google sheets – Query sum for single pairs of rows

= QUESTION (A1: C, "select A, B, sum (C)
where C is not null
group by A, B
label sum (C) & # 39; Days & # 39; ", one)

0


= ARRAYFORMULA (IFERROR ((VLOOKUP (UNIQUE (A1: A & B1: B), QUERY ({A1: A & B1: B, A1: C},
"select Col1, Col2, Col3, sum (Col4)
where Col4 is not null
group by Col1, Col2, Col3
label sum (Col4) & # 39; Days & # 39; ", 1), {2,3,4}, 0))))

0

How to refer to a dynamic range in Google Sheets Script?

I have a sheet where the filter originally started in row 2, so my range was A2: J. As I added and deleted rows / columns, my range has changed and keeps changing as I edit the sheet.

Is there a way to name a dynamic range so that if I add / delete columns, my functions work correctly? Something like Sheets / Excel will update the formulas if you edit a cell reference.

I have sort by Y place the cursor in the cell functions that are executed onOpen. However, if I do not manually update the range, everything is ruined every time I open the document. For example, a few days ago my range was "A6: J" + lr; my filter was in row 6. I added another header row, making my filter row 7, but the script reference A6 as the rank to order. This ruined my data. It's driving me crazy!

I would appreciate some idea on how to refer to a dynamic range. I'm not sure if INDIRECT or ADDRESS would work?? Not sure if this answer makes sense?

    onOpen () {function
var ss = SpreadsheetApp.getActiveSpreadsheet (). getSheetByName ("DATA");
var lastRow = ss.getLastRow ();
var lastColumn = ss.getLastColumn ();
var lastCell = ss.getRange (lastRow, lastColumn);
var range = ss.getRange ("A7:" + lastRow);
Logger.log (ss.getRange (lastRow, lastColumn) .getDataRegion (). GetA1Notation ());
var columnToSortBy = 2;
var range = ss.getRange ("A7: J" + lr);
range.sort ({column: columnToSortBy, ascending: true});
var r = Math.max.apply (Math, ss.getRange (& # 39; B: B & # 39;). getValues ​​()
.map (function (d, i) {
yes (d[0]) returns i + 1;
}). filter (function (d) {
return d;
})) + 1;
ss.setActiveRange (ss.getRange (r, 2))
}

Google Apps Script: Update the rows of other sheets based on the pasted data.

I have a worksheet with several sheets for social network analysis purposes. I would like to be able to paste data from a csv and export it daily on a sheet and have the data analyzed and copied to other sheets within the same worksheet.

For example, I paste 5 rows on the "Input" sheet with 5 different hashtags and their performance statistics. I would like to create a script or something that finds the hashtags within the more than 40 sheets and then paste the data from the input sheet in the corresponding row with that same hashtag. so the hashtag statistics 1 can be pasted on sheet 15 and row 40, while hashtag 1 statistics can be pasted on sheet 20 and 10th row.

I would have to match the input hashtag with a column with the same hashtag on a different sheet and then paste its input performance statistics on the sheet that has the same hashtag that was entered.

I can not simply make an IF declaration and = reference the cells in the input sheet because the data in the input sheet will be replaced every day and I can not lose the data that was previously retrieved from the input sheet.

Could someone help me achieve this?

How can I send an email, with an attachment of the user's local storage, using Google Sheets Script?

I'm new to Google Script but I have enough experience in Excel VBA. I am trying to set up a Google sheet to allow the sending of a document given by an email attachment to a series of recipients.
I have the part of the email running and I can see how to attach a document as a PDF of Google Drive, but I need to know how to offer the user a selection of files in their own local storage, that is, on their own hard drive of the computer. It would be great if you could encode something that would offer the user a selection screen like MS Explorer from which they could select a file to attach. I realize that this may not be immediately possible, and that the file must first be loaded into Google Drive. If so, it would be great to automate that too.
I would really appreciate any help with this please.