CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsweb-applications
MOO
MOO

Reputation: 193

How to update row data of google spreadsheet using google app script

I am facing a problem on how to update the row of data in my database(google spreadsheet) using Google app script. Basically, I have a cell in the spreadsheet that has the value "Pending Approval" in it. When the script is executed, I want it to change the value in that cell to "Approved". I did lot of search but I can't figure out, how to do it correctly. I am newbie in this. Hope you can help me. Thanks!

Code.gs

 function doGet() {
  return HtmlService
      .createTemplateFromFile('Index')
      .evaluate();
}

function getData() {
  return SpreadsheetApp
      .openById('17lKIhONfEeNogsBhKtGr4zVaLgH0_199-3J3-0tAdcE')
      .getActiveSheet()
      .getDataRange()
      .getValues();
}

function setApproved(ldap) 
{
 // set the correct id and get the database
 var id= SpreadsheetApp.getActiveSpreadsheet().getId('17lKIhONfEeNogsBhKtGr4zVaLgH0_199-3J3-0tAdcE');
 var sss = SpreadsheetApp.openById(id).getActiveSheet();

 // Get all the data from the sheet
 var data = sss.getDataRange().getValues();

 // Get the headers and get the index of the ldap and the approval status
 // using the names you use in the headers
 var headers = data[0];
 var ldapIndex = headers.indexOf('ldap'); 
 var statusIndex = headers.indexOf('approvalstatus');

 // Declare the variable for the correct row number
 var sheetRow;

 // Iterate data, look for the correct row checking the ldap, start from 1 as 0==headers
 for( var i = 1 ; i < data.length; i++ )
 {
   var row = data[i];
   if(row[ldapIndex] == ldap)
   { 
     // You have found the correct row, set + 1 because Sheet range starts from 1, not 0
     sheetRow = i +1;
     // We have found the row, no need to iterate further
     break;
   }
}
 // Also set statusIndex +1, because index in array is -1 compared to index in sheet
 ++statusIndex;
 //Set the value
 sss.getRange(sheetRow, statusIndex ).setValue('Approved'); 
}

Index.html

 <html>
  <head>
    <base target="_top">
  </head>
  <body>
    <div  id="tables">
      <? var data = getData();?>
      <table style="display: inline-block; border: 1px solid; float: left; " id="tableShift1">
      <caption style="">Shift1</caption>
          <th style="border:2px solid black;">   Ldap   </th>
          <th style="border:2px solid black;">   Date   </th>
          <th style="border:2px solid black;">   Action   </th>
        <? for (var dataList = 1; dataList < data.length; dataList++) {
             ?>
          <tr >
            <td style="border:1px solid black;"><?= data[dataList][0] ?></td>
            <td style="border:1px solid black;"><?= Utilities.formatDate(new Date(data[dataList][1]),"GMT+800", "yyyy-MM-dd"); ?></td>
            <td style="border:1px solid black;"><button onclick='setApproved()' id='ldap'>Approve</button> <button onclick='functionDecline()' id='button1'>Decline</button></td>
          </tr>
        <?   
          } ?>
        </table>
    </div>
  </body>
 <script>
 </script>
</html>

Upvotes: 7

Views: 27707

Answers (4)

Keven Andrade
Keven Andrade

Reputation: 1

I found a easy and quick solution. Just change this:

var id= SpreadsheetApp.getActiveSpreadsheet().getId('17lKIhONfEeNogsBhKtGr4zVaLgH0_199-3J3-0tAdcE');

To this:

var id= SpreadsheetApp.getActiveSpreadsheet().getId('17lKIhONfEeNogsBhKtGr4zVaLgH0_199-3J3-0tAdcE').getSheetByName('NAME_OF_UR_SHEET');

Upvotes: -1

Egarcia Egarcia
Egarcia Egarcia

Reputation: 11

<td style="border:1px solid black;"><button onclick='google.script.run.setApproved("<?= data[dataList][0] ?>")' id='ldap'>Approve</button>

Upvotes: 1

Gem-oh
Gem-oh

Reputation: 199

You don't need to use getId() to get the Id because you already know.

var id = '17lKIhONfEeNogsBhKtGr4zVaLgH0_199-3J3-0tAdcE';

  • On your Index.html I observed that you did not use google.script.run to call a function to the server google.script.run.setApproved([parameter])

  • Your onclick() function call did not pass any parameter.

    <td style="border:1px solid black;"><button onclick='google.script.run.setApproved("jack")' id='ldap'>Approve</button> </td>

Upvotes: 0

FatFingersJackson
FatFingersJackson

Reputation: 812

Ok, as you have tagged the question as Google-App-Script, I presume you know how to call the function from the client side.

So as the ldap does not occur in the database twice, you could use it as an id with a function like following:

function setApproved(ldap) 
{
 // set the correct id and get the database
 var id= SpreadsheetApp.getActiveSpreadsheet().getId();
 var sss = SpreadsheetApp.openById(id).getActiveSheet();

 // Get all the data from the sheet
 var data = sss.getDataRange().getValues();

 // Get the headers and get the index of the ldap and the approval status
 // using the names you use in the headers
 var headers = data[0];
 var ldapIndex = headers.indexOf('ldap'); 
 var statusIndex = headers.indexOf('approvalstatus');

 // Declare the variable for the correct row number
 var sheetRow;

 // Iterate data, look for the correct row checking the ldap, start from 1 as 0==headers
 for( var i = 1 ; i < data.length; i++ )
 {
   var row = data[i];
   if(row[ldapIndex] == ldap)
   { 
     // You have found the correct row, set + 1 because Sheet range starts from 1, not 0
     sheetRow = i +1;
     // We have found the row, no need to iterate further
     break;
   }
}
 // Also set statusIndex +1, because index in array is -1 compared to index in sheet
 ++statusIndex;
 //Set the value
 sss.getRange(sheetRow, statusIndex ).setValue('Approved'); 
}

I hope something like above works for you, did for me. I hope it is not too messy either, a quick example I came up with. Do let me know if you have any questions !

EDIT: I added the 'break' to the loop, as I forgot it first and seems it already backfired for you.

EDIT: I will add an image for an example. The sheet demonstrates what kind of sheet is used for the example and what the sheet looks like after I have once run setApproved('jaska') : example

Upvotes: 8

Related Questions