[Google Apps Script] Manipulate with your Google Spreadsheet

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.

Table

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.

1	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 to first rows or first columns.

1 2	Sheet.insertRows(1) Sheet.insertColumns(1)

Use the getLastRow() and getLastColumn() to get last value in spreadsheet and insert it 5 more row or column.

1 2	Sheet.insertRows(Sheet.getLastRow()+1,5); Sheet.insertColumns(Sheet.getLastColumn()+1,5);

Use the getMaxRows() and getMaxColumns() to get the max row or column in spreadsheet.

1 2	Sheet.insertRows(Sheet.getMaxRows()); Sheet.insertColumns(Sheet.getMaxColumns());

4. Update

Update the value of the data.

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

5. Delete

Delete the value of the data.

1	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