apply a script on only a few sheets

function onEdit(e) {
var feuillelundi = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Lundi”);
var range = SpreadsheetApp.getActiveSheet().getActiveRange();
var cours, joueurs, coach, motifannul
var ligne = range.getRow();

for (var groupe=3;groupe<118;groupe = groupe +12) {
cours = feuille.getRange(ligne,groupe);
coach = feuille.getRange(ligne,groupe – 1);
motifannul = feuille.getRange(ligne,groupe + 1);

    for (var j=2;j<9;j++) {
        joueurs = feuille.getRange(ligne,groupe + j);
             if (!cours.getValue()) {
                  coach.setValue('REMPLIR');
             }
             if (cours.getValue()) {
                  motifannul.setValue('REMPLIR');
             }          
             if (!cours.getValue() && joueurs.getValue()) {
                     joueurs.setValue('FALSE');
             }
    }
}

}

Go to Source
Author: icekio

Copy from individual cell in Google Sheets Table Chart

I’m using appscript to build and insert a table chart into a sheet when a button has been pushed.

var chart = sheet.newChart()
.setChartType(Charts.ChartType.TABLE)
.addRange(outrange2)
.setPosition(2, 12, 35, -5)
.setOption('height', 445)
.setNumHeaders(1)
.setOption('page', 'enable')
.build();
sheet.insertChart(chart);

The chart gets created and inserted properly though my users are unable to interact with the contents of this table directly, they can only scroll and change pages.

I have checked to see if there were any required options that need to be enabled when before building the chart, the appscript guide references me to this page when advising on options:

https://developers.google.com/chart/interactive/docs/gallery/table

In the reference table and the overview above it states that “Users can select single rows either with the keyboard or the mouse. Users can sort rows by clicking on column headers. The header row remains fixed as the user scrolls. The table fires a number of events corresponding to user interaction”

The table that appears in my sheet is not like the one that appears in the reference and I am unable to copy text from the cells or select individual rows in the newly generated table. When attempting to interact with this table from google sheets it seemingly only allows us to resize and scroll up and down the table.

Are there ways to get around this or some attributes that I am missing to allow us to interact with individual entries from the table chart?

Go to Source
Author: Stephen Catchpole

How to auto-reply to reply-to field?

I would like send auto-reply mails to email addresses written in reply-to field. How this can be achieved?

  Sample Mail Received :
 
 from:  abc@gmail.com
 reply-to:  def@gmail.com
 to:    ghi@gmail.com
 date:  Aug 31, 2020, 12:43 AM    

By using the template and filter options I am able to send auto-reply e-mails to addresses written in from field (abc@gmail.com in this case). But I am not able to figure how to send auto-reply to addresses written in reply-to field (def@gmail.com in this case).

Go to Source
Author: deepak sharma

FedEx Tracking in Google Sheets

I work for a company that sends packages out daily. We track the numbers in a Google sheet that populates via a Google form we use.

Recently we have had some packages go missing and I have been tasked with setting something up that allows us to watch the packages and make sure they are not getting lost in an effort to be proactive.

My thoughts were to do some sort of status updates with the tracking number, currently using lovelyAPI’s package tracker add on, but I would like something custom if possible.

enter image description here

Column x, y, z, aa, and ab are all populating through the package tracker and I have a formula to hyperlink the FedEx site and insert the value in F.

Is there a script that can do the same thing for me using the FedEx API?

I would also like to go a step further and have some sort of conditional formatting that would highlight the row in a certain color if the date I have in column A is older than 2 days and column Y does not return “delivered” or something like that. Can anyone help me out with this?

Trying to automate the whole thing but I am not but a mere peeon with little to no knowledge of coding.

Go to Source
Author: Noah Armstrong

Google Apps Script: Check, Copy, Email and Delete Duplicate row from Google Form Submission

Need your help on this, i am totally new to the Google Apps Script and struggling with the small function of following:-

  1. Check for the duplicate on specific field (only one field) value submitted from Google Form
  2. If no duplicate found, do nothing.
  3. If duplicate found, script will copy the entire duplicate row found to the another sheet in the same spreadsheet.
  4. Script will then send an email to the responder automatically.
  5. Script will then delete the duplicate row and wait for the next submission.

I’ve a snippet of the modified script getting from the online resources, and play around it but unfortunately looks like it went so much wrong and not works as per expected.

What i have:-

  1. In my Google Form, I’ve only capture one field value as Short Answer text field named Serial
  2. In my Form Responses sheet only contains three column (timestamp, Serial & Email address collected from those responded into the form)
  3. Duplicate Response sheet, contains the same header as in Form Responses (will use to copy the entire duplicate records row here before actually delete it from the Form Responses Sheet).
  4. My code is as below:-
function removeDuplicates()
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
  var targetsheet= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Duplicate Responses")
  var targetNewRow = targetsheet.getLastRow() + 1;
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  var indexOfStatusColumn = 1;
  var newData = [];
  data.forEach(function (row, rowI){
    var isDuplicate = false
    if (newData.length)
    newData.forEach(function (dup, dupI)
    {
      if (dup[3] == row[3])
      {
        if (row[indexOfStatusColumn] == dup[indexOfStatusColumn])
        {
          targetsheet.appendRow(row);
          sendEmail(row[2],row[1]);
          isDuplicate = true;
 
        }
        return (false);
      }
    })
    if (!isDuplicate)
      newData.push(row);
  });
  dataRange = sheet.getRange(2, 1, dataRange.getLastRow() - 1, dataRange.getLastColumn());
  sheet.deleteRow(dataRange.getLastRow() - 1);
  //dataRange.clearContent();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

function sendEmail(emailaddr, sn) {}

Issue:-

  1. The initial code was written originally with clearContent() function, but apparently i found once the contents is cleared and new submissions is made it will actually appended only next after the empty row which caused it left the duplicate row empty and not reusable.

  2. The duplicate row copied to the another sheets “Duplicate responses” is not as expected, it will also copied non-duplicate records intermittently when mass submission is made.

Appreciate your help on this please!

Go to Source
Author: Fariz Azmi

Is there any way to limit editing in Google Docs?

I’m looking for any way to set the character limit in Google Docs (specifically MS Word-like text processor).
So, for example, I have a cell in the table and I want to only allow putting 2000 characters or less into the cell.
I wonder if there’s such functionality in Google Docs, maybe it’s possible with a script or some other way?
Thank you!

Go to Source
Author: Alex

Simplify app script for google sheets

Hi anyone knows how I can simplify this code?
This is working, however it takes time to update the datetime column

function onEdit(e) {
var row = e.range.getRow();
var ws = “Consults Form Responses”;
if(row > 1 && e.source.getActiveSheet().getName() === ws)
{addDateAnswered(e);}
AllFormat();
}

function addDateAnswered(e){
var row = e.range.getRow();
var validity = e.source.getActiveSheet().getRange(row,14).getValue();

if(validity == “VALID” || validity == “INVALID”)
{setDateAnswered(e);}
else if(validity == “DUPLICATE” || validity == “NFA”)
{e.source.getActiveSheet().getRange(row,17).setValue(validity)}
else
{e.source.getActiveSheet().getRange(row,17).setValue(“PENDING”);}

var AllRange = ws.getRange(“A2:P”);
AllRange.setVerticalAlignment(“middle”);

var AllCenter = ws.getRange(“A2:A” && “F2:F” && “J2:K” && “M2:M”);
AllCenter.setHorizontalAlignment(“center”);

var TestLeft = ws.getRange(“O”);
TestLeft.setHorizontalAlignment(“left”);
}

function setDateAnswered(e){

var row = e.range.getRow();
var col = e.range.getColumn();
var TimeStamp = Utilities.formatDate(new Date(), ‘GMT+8:00’, ‘MM/dd/yyyy hh:mm:ss a’);

if(col === 14 && row > 1)
{e.source.getActiveSheet().getRange(row,17).setValue(TimeStamp);}
}

Go to Source
Author: KB John

How to use this google api console developer tools script for copy clone youtube watch later playlist?

How to use this google api console developer tools script for copy clone youtube watch later playlist? The instructions are old. Now google api console developer tools looks different so i cant find correct way to implement it.

http://www.art4software.com/2014/09/youtube-api-how-to-copy-the-watchlater-list/

Go to Source
Author: Kangarooo

Automatically Increase Document Number/Cell Number on open

I would like to be able to click on something which will open a new google sheet based on a template. I would need cell A1 to contain a number which increases by 1 every time the template is opened. Ideally I would like the title to match this new number.

I am working on developing a purchase order form and want to make sure that no matter who opens the template sheet, they will be given their own PO number in order of when they opened it to avoid multiple people using the same number.

I have limited scripting experience so i have been performing a lot of google searches and trying to map out how to ask this properly. I have tried a macro that makes the title the same as cell A1 but no luck. I still have no idea how to automatically generate an incremental number for the title based on the time it was clicked.

Go to Source
Author: Oliver H

Get Table Name With Apps Script

I have two tables, namely Table A and Table B. Each table I want to identify the name of the table in column C
matches the table names that have been defined in column A.

The script I’ve made is like this:

tableName () function {
  let ss = SpreadsheetApp.getActiveSpreadsheet ();
  let cColumn1 = ss.getRange ('C3: C7');
  let cColumn2 = ss.getRange ('C11: C15');

  let tableName1 = ss.getRange ('C2'). getValue ();
  let tableName2 = ss.getRange ('C9'). getValue ();

  cColumn1.setValues   (tableName1);
  cColumn2.setValues   (tableName2);
}

But my script is still manual, we have to specify the start line and end line of the table.
To identify the name of the table I’ve ever made with a formula, so with this formula, we don’t need to specify the position
the name of the reference table.

= ARRAYFORMULA (IF ((B: B = "") + (B: B = "Name") ,, VLOOKUP (ROW (A: A), IF (LEN (REGEXEXTRACT (A: A, "TD+") ), {ROW (A: A), REGEXEXTRACT (A: A, "TD+")}, 0), 2)))

This formula is very heavy if the amount of data is large.

My Spreadsheet.

Go to Source
Author: Lutfi Creativesys

Extract data from web with google script

I’m trying to use the function below (source) to avoid using importxml in Google Sheets:

function importRegex(url, regex_string) {
  var html, content = '';
  var response = UrlFetchApp.fetch(url);
  if (response) {
    html = response.getContentText();
    if (html.length && regex_string.length) {
      var regex = new RegExp( regex_string, "i" );
      content = html.match(regex)[1];
    }
  }
  Utilities.sleep(1000);
  return content;  
}

I want to extract stock prices and day change from Yahoo Finance, for example from: https://finance.yahoo.com/quote/TSLA/, but I can’t get the regex_string right. I have tried several strings but none seens to work. Some one can help me please?

=importRegex("https://finance.yahoo.com/quote/TSLA/", "<span class=""Trsdu(0.3s) Fw(b) Fz(36px) Mb(-4px) D(ib)"" data-reactid=""32"">(.*)</span>")

Go to Source
Author: Caseyblack999

Can automatic send emails when checkbox = True?

I’m looking for an script can automatic send emails when checkbox is True.
This is my format example:
enter image description here

What i want is when im clicking on checkbox = True at F2, i can recieve an email with excactly contents from A2 to E2, looks like this:

  • Email subject= [Approved] Form

  • Email message= contents below

  • Timestamp: 01/06/2020 + times
  • Machine/Equiment require: PU1602
  • Plant to require: RF
  • Team to fix: Ele
  • Form no: F01

The emails address can be set on the scripts too.
So please help to this. I lost my hair to try someway, but its hard to me.
This is the sample spreadsheet of my project:

my samle spreadsheet project

Go to Source
Author: NKlaus