If you are looking for the script to create, insert, append, delete (CRUD) the google sheets with the google app script. This article will show you how to manipulate the google app script with your google spreadsheet.

1. Initiate your Script

Let’s start your first google App Script, Click here.

Initiate the spreadsheet & tab.

  let SpreadSheet = SpreadsheetApp.openById("the sheet id");
  let Sheet = SpreadSheet.getSheets()[0];
  let SheetByName = SpreadSheet.getSheetByName("sheet1");

2. Read

Read the specific column.

  Sheet.getRange(1, 1).getValues();

Read the last row or column.

  // the number of last row
  let LastRow = Sheet.getLastRow(); 

  // the number of last column
  let LastColumn = Sheet.getLastColumn(); 
  Sheet.getRange(LastRow+1, 1).getValues();

3. Write

Insert a row in the specific place of a google spreadsheet.

  let insertRowNumber = 3
  Sheet.insertRows(insertRowNumber)

4. Update

Update the value of the data.

  let LastRow = Sheet.getLastRow(); 
  Sheet.getRange(LastRow+1, 1).setValue(name);

5. Delete

Delete the value of the data.

  Sheet.deleteRow(LastRow+1,1)

6. Other

Format the text in google spreadsheet

spreadsheet.getActiveRangeList()
  .setFontWeight('bold')
  .setFontStyle('italic')
  .setFontColor('#ff0000')
  .setFontSize(18)
  .setFontFamily('Montserrat');

Set Sheet Tab color.

  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {

    // set the color for red
    sheet.setTabColor("ff0000");
    // remove the color
    sheet.setTabColor(null);
  });

Debug for the google app script.

function logTimeRightNow() {
    const timestamp = new Date();
    Logger.log(timestamp);
    console.log(timestamp);
}

7. Try it with Simple example

It is a function for hash the data in the google spreadsheet.

function MD5 (input) {
  var rawHash = Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, input);
  var txtHash = '';
  for (i = 0; i < rawHash.length; i++) {
    var hashVal = rawHash[i];
    if (hashVal < 0) {
      hashVal += 256;
    }
    if (hashVal.toString(16).length == 1) {
      txtHash += '0';
    }
    txtHash += hashVal.toString(16);
  }
  return txtHash;
}

You can use the function simply in the google spreadsheet.

---

Thanks for your reading. I hope this article will help you better understand google app script.

References:

• Guide to storing data in spreadsheets