CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Hippo
Hippo

Reputation: 5

Can I copy data from one spreadsheet to a new spreadsheet?

I am currently working on a script to automatically send an email when a cell is updated based on a specific value of cells. Now I want to take those same values that I am emailed and populate them into a new spreadsheet and post a timestamp of when that row was updated. In the new SS it will be A(timestamp), B, C, D, E ( B-E will be the data that is automatically emailed).

With the help of another user on here I was able to get the automated email system working. I found several posts asking similar questions which I tried to piece together to see if I could get this working but I had no success. I kept running into blocks in the code because I don't know all the proper syntax for google-apps-scripts. I tried copyTo(), duplicateSheet(). But the problem is I only care about updating the one row at a time as the specific cell gets updated on the row. And all I found were posts about copying entire sheets of data onto a new spreadsheet. I thought I would have to define it in the above and then add the copyTo() in the if statement but everytime I tried it the if statement broke and would get error message in the code.

(I also have an onEdit trigger in the project triggers)

function sendNotification(e){ 

  var ss = e.source.getSheetByName('Datasheet'); //defines the source of 
where to look
  var cell = e.range.getA1Notation(); 
  var row = e.range.getRow(); //from the range gets the row(is important 
for calling the control owner and control ID on line 15 and 16)
  var col = e.range.getColumn(); //from the range gets the 
column(important when pulling specific columns of information)
  var cellvalue = e.range.getValue(); //this pulls whatever is inside of 
the cell. (so 'NAME' in column i)
  var subject = 'SUBJECT: '+ ss.getSheetName(); //tells the program what 
to put in the subject line of the email (ss.getSheetName() gets the name 
 of the tab of the data)
  var name = ss.getRange(row, 9).getValue(); //get column 9 for current 
row (column 9 is column i which is the control certifier)



  if (name === 'NAME' && (col === 23 || col === 24 || col === 31 || col 
=== 32) === true) {  //states: if the cell  in column i = TRUE, AND column 
(w)23, (x)24, (ae)31 OR (af)32 = changed/updated THEN execute command 
below
   var control = ss.getRange(row, 2).getValue(); //get value for column B 
in updated cell row
   var owner = ss.getRange(row, 8).getValue();   //get value for column H 
in updated cell row





    //line 21-35 is the email formatting
    //MailApp.sendEmail() sends the email if line 14 = TRUE
    // to: who the email is sent too
    //Subject = subject defined above
   //htmlBody = what is in the body of the paragraph sent


   MailApp.sendEmail({
      to: "EMAIL",
      subject: subject,
      htmlBody: "The following cell has been updated: <br><br>"+
      "<br><br>" + "The control is: " + control +
      ", <br><br>The owner is: " + owner +  
      "<br><br>The change was: " + cellvalue + "<br>" + 
      "<br><br>Thank you. <br><br><br><br>" +
        })
  }
}

Basically I would love it if when column W, X, AE or AF get updated. on top of doing what it does currently of sending me an email of the information. To also update all that information into a new row on a completely different spreadsheet with a timestamp of when that was edited so I can log it.

Upvotes: 0

Views: 78

Answers (1)

Cooper
Cooper

Reputation: 64062

I got this to work within my limited understanding of what your doing.

function notify(e){ 
  var sh=e.range.getSheet();
  var dsh=e.source.getSheetByName('Sheet161');
  var name=sh.getRange(e.range.rowStart,9).getValue();
  var col=e.range.columnStart;
  //e.source.toast('Start');
  if (sh.getRange(e.range.rowStart,9).getValue()=='NAME' && sh.getName()=='Selection') {
    //e.source.toast('Flag1');
    if (col==23 || col==24 || col==31 || col==32) {
      //e.source.toast('Flag2'  +  sh.getRange(e.range.rowStart,col).getValue());
      if(sh.getRange(e.range.rowStart,col).getValue()==true) {
        //e.source.toast('Flag3');
        var control=dsh.getRange(e.range.rowStart, 2).getValue();
        var owner=dsh.getRange(e.range.rowStart, 8).getValue();
      }
    }
    var html="The following cell has been updated: <br><br>"; 
    html+="The control is: " + control + ", <br><br>The owner is: ";
    html+=owner + "<br><br>The change was: " + e.value + "<br>" + "<br><br>Thank you. <br><br><br><br>";
    //MailApp.sendEmail({to: "EMAIL",subject: subject,htmlBody:html});
    var userInterface=HtmlService.createHtmlOutput(html);
    SpreadsheetApp.getUi().showModelessDialog(userInterface,'data' );
  }
}

Upvotes: 0

Related Questions